• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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?

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
tjyoung
Asked:
tjyoung
  • 6
  • 4
1 Solution
 
Ray PaseurCommented:
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
 
tjyoungAuthor Commented:
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
 
tjyoungAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
tjyoungAuthor Commented:
assuming I have both marked as 'UNIQUE'
0
 
tjyoungAuthor Commented:
Bit of investigating. Does this look right?
 sample.jpg
0
 
Ray PaseurCommented:
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
 
tjyoungAuthor Commented:
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
 
Ray PaseurCommented:
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
 
tjyoungAuthor Commented:
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
 
Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now