mySQL update if record is unique

Posted: Sat Aug 19, 2006 6:10 pm

I am using a form to insert data into a table - namely a domain and date.
I quickly realised that by using INSERT it is possible to add the same domain more than once and thats a no-no because I don;t want the domain to be listed more than once.
I am using the following code for my INSERT statement

Code:




$ustatus = $_POST['ustatus'];


$uurl = $_POST['uurl'];


$actioned = date('Y-m-d H:i:s');


//snipped





$add_all = "INSERT INTO `test_table` (`key`,`value`,`used`,`last_mod`) VALUES ('$ustatus','$uurl','1','$actioned')"; 


   mysql_query($add_all) or die(mysql_error());

The DB field that holds the domain data is 'value' and the variable that holds the url is $uurl - how can I alter this so that I can use UPDATE to ensure the data is only submitted if the domain ($uurl) is unique?

Posted: Sat Aug 19, 2006 6:16 pm

Do a find where the domain field = your $uurl variable.

Check for numrows. Mysql_numrows or some such syntax.

If it's greater than 1 you have an error condition in the existing table.

If it's = to 1 then you don't want to insert the record. You could echo an error message to the screen or just bypass the insert.

If it's 0 then you are good to go with your insert statement.

Posted: Sat Aug 19, 2006 6:41 pm

Thanks.
Not sure how I'm going to do that but I guess more reading is in order.

I could cheat and set the DB field attribute to 'unique' which would throw a mysql error but thats not exactly user friendly. I had also looked at using INSERT IGNORE but that doesnt seem to fit the bill quite right.

I guess I'll have to retreive the data first as you suggested and then compare it to my $uurl then take appropriate action as required.

registered · Posted: Sat Aug 19, 2006 7:08 pm

Quote:

I guess I'll have to retreive the data first as you suggested and then compare it to my $uurl then take appropriate action as required.

That is the proper way to do it.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Guardian2003 wrote:

Thanks.
Not sure how I'm going to do that but I guess more reading is in order.

I could cheat and set the DB field attribute to 'unique' which would throw a mysql error but thats not exactly user friendly. I had also looked at using INSERT IGNORE but that doesnt seem to fit the bill quite right.

I guess I'll have to retreive the data first as you suggested and then compare it to my $uurl then take appropriate action as required.

That's not cheating. It's the most correct way to do this. Instead of throwing the standard error, just trap it and display your own message.

Posted: Sun Aug 20, 2006 1:02 am

Thanks guys!

Posted: Sun Aug 20, 2006 8:48 am

I see that we have our differences of opinions... Wink

Well, that is what makes this Team so amazing!

Posted: Sun Aug 20, 2006 1:54 pm

Best of both, as they say.
I'm using a mysql_query to retrive the data from the column I'm interested in and comparing it with value of the $var - if there is a match a message is displayed and the form is rest.
I'm also using mysql_error to report any attmept to post data to the column that is not unique.
So I guess thats pretty well covered now, thanks guys.

Now I have to do some error checking to ensure the vars the form is accepting are not empty and conform to the data that is expected.

Just for grins and giggles I checked the whole code in FF with the TIDY plugin and got well over 200 errors <cough> but a quick deft manipulation with my trusty editor brought that down to under 40 in next to no time (most of those remaing are core nuke problems anyway).

Thats me done for today now or I risk breaking out in a sweat.
Thanks to 'M' for the 'form' pointer Smile