?
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
Medium Priority
?
248 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 111

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 111

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 111

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

840 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