Page 1 of 1

How to tell if row doesn't exist in table?

Posted: Fri Mar 16, 2007 11:28 pm
by SmokyBarnable
I'm using this code to update my ebay auctions based on the auction id. This works fine but occasionally it trys to update an auction that is not in my table. I want to make it so if the auction isn't there then insert it. What is a good way to find out if the auction id isn't in the table?

Code: Select all

tep_db_query("UPDATE `auction_list` SET `starttime` = '".$itemstarttime."',`endtime` = '".$itemendtime."', `amount` = '".$amountleft."' WHERE `auction_id` = '".$itemid."'");
Do I need to select * from the table and make a loop?

Posted: Fri Mar 16, 2007 11:38 pm
by John Cartwright
a couple ways to do this,

a) Check using another query prior to running the update if the row exists, using count(`id`) or whatever.
b) Attempt to run the update, then confirm that records were updated using mysql_affected_rows()
c) A clever use of the INSERT ON DUPLICATE KEY UPDATE syntax could potentially do this all in a single query

Posted: Fri Mar 16, 2007 11:39 pm
by feyd
Look at the number of affected rows? Note that rows where the new values match the existing values of the record will generally not show in affected row information.

What many systems do is attempt to insert. When an error flags based on some unique identifier matching an existing identifier, the application will attempt to update.

Posted: Sat Mar 17, 2007 12:15 am
by pickle
Isn't this what the REPLACE statement is for?

Posted: Sat Mar 17, 2007 12:26 am
by feyd
REPLACE pretty much only exists in MySQL.