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
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 110

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 110

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 110

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 110

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

696 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