Solved

Check row values and if 2 fields in a single row match values I'd be inserting, modify it, otherwise, create a new row.. but how?

Posted on 2011-09-11
10
242 Views
Last Modified: 2012-05-12
Hi,
I have a 'subscriber' table that contains the phone number they subscribed with and what account it belongs to. The subscriber can change his subscription status by text message. What I'm having trouble with is ensuring that there is only one row that contains that phone number associated to that particular account id.

Lastly the subscriber could possibly subscribe to more than one account.

So: how does one go about creating a query when doing an insert or what have you that checks the account id and the phone number and if they exist together, it updates the values, otherwise, if they don't exist together, they create a new row?

I can access the 2 variables for the query fine (that being account_id and CallFrom), just don't know how to compare/update/insert.

Thanks for your help.

0
Comment
Question by:tjyoung
  • 6
  • 4
10 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 36519424
You can mark a column UNIQUE and MySQL will throw error #1062 if your script tries to insert a duplicate value in the column.  You can make a UNIQUE index over more than one column.  Which would probably be good, since several family members or employees might have the same phone number.
0
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 36519447
As always, perfect advice. I wasn't sure what error etc. it may/may not throw and if you can set multiple fields. Thats great.

Thanks.
0
 
LVL 1

Author Comment

by:tjyoung
ID: 36519498
Not sure I get UNIQUE as it applies to 2 columns. I need to ensure that if my script tries to insert duplicate values in BOTH columns at the same time, then throw the error. Is that how it works? Or will it throw the error if the script tries to insert duplicate in either of them?

I'm trying to get where one or the other can be duplicated, but not both together in one row.

For instance: a person may subscribe with the same number to two different accounts and that would be ok. I just don't want him to subscribe twice to the same account using the same number.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:tjyoung
ID: 36519501
assuming I have both marked as 'UNIQUE'
0
 
LVL 1

Author Comment

by:tjyoung
ID: 36519516
Bit of investigating. Does this look right?
 sample.jpg
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519528
insert duplicate values in BOTH columns at the same time, then throw the error. -- yes, that is how I understand a UNIQUE index works.

These two columns, taken together, are not unique
1 Foo
2 Foo

This insert will succeed
3 Foo

This insert will fail
3 Foo

Not sure I understand the structure illustrated in the picture, but I think subscribe looks right.  You might want to try SHOW CREATE TABLE and see if it looks intuitively correct.

Just curious - is this a Twilio app?

All the best, ~Ray
0
 
LVL 1

Author Comment

by:tjyoung
ID: 36519546
HI,
Yes I've been plunking away on this app for what seems like forever (been a great learning experience). I'm trying to get to 'proof of concept' for a demo this Thursday and if all is good, get it built for real. Been using code igniter and twilio api and a couple others.

Whats the difference between:

This insert will succeed
3 Foo

This insert will fail
3 Foo
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519552
The second insert will fail precisely because the first insert succeeded.  I am channeling the two-column UNIQUE index.

Twilio is a great service!
0
 
LVL 1

Author Comment

by:tjyoung
ID: 36519575
Yes I've been running a number of small apps using twilio (you of course helped me with one of them) and you wouldn't believe how much I salvaged from your original code to get other items to work :)

Twilio invited me down and would pay for my entrance to their conference in september but from Canada to San Francisco airfare is a bit pricey so I had to pass. Nice of them to offer though. We spend a bunch with them for the dealerships etc.

Great guys/service. Hope they don't sell out soon.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36519690
Why don't you ask them to pay the airfare or at least split the airfare.  I'll bet if you are willing to do a presentation, they would consider it.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question