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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
SmokyBarnable
Forum Contributor
Posts: 105
Joined: Wed Nov 01, 2006 5:44 pm

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

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Isn't this what the REPLACE statement is for?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

REPLACE pretty much only exists in MySQL.
Post Reply