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
239 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 108

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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 108

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 108

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 108

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
htacces issue 2 39
WooCommerce Sort by Date 4 6
html input clean up 3 30
How Can I Use otf Custom Font with TCPDF 4 4
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now