Re: [PHP] Update or add?
Brian Dunning wrote: I have a table where I want to update each record with today's date as it's hit, or add the record if it's not in there: +--+-++ | id | creation_date | last_hit | +--+-++ I'm trying to do this with a minimum of hits to the db, so rather than first searching to see if a matching record is in there, I thought I'd just go ahead and update the matching record, check to see if it failed, and if it failed then add a new one, like this: $id = $_GET['id']; // Update $query = "update table set last_hit=NOW() where id='$id'"; $result = mysql_query($query); // Add if(the update failed) { $query = "insert into table (id,creation_date,last_hit) values ('$id',NOW(),NOW())"; $result = mysql_query($query); } What's the fastest way to check if the update failed? You have a few options as others have pointed out, but I'll post this as an option anyway. Other db's don't have replace into or the "on duplicate key" option either, so you'd need to do something like this: $query = "update .."; $result = mysql_query($query); if (mysql_affected_rows($result) == 0) { // do the insert. } -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Update or add?
Hi All, there is not-so-used format of SQL format that is INSERT INTO table SET col1=data1, col2=data2... ON DUPLICATE KEY UPDATE col1=data1, col2=data2...; you have to define a primary or unique key in the table and all will work well. for more info http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html Cheers /V Brian Dunning wrote: I have a table where I want to update each record with today's date as it's hit, or add the record if it's not in there: +--+-++ | id | creation_date | last_hit | +--+-++ I'm trying to do this with a minimum of hits to the db, so rather than first searching to see if a matching record is in there, I thought I'd just go ahead and update the matching record, check to see if it failed, and if it failed then add a new one, like this: $id = $_GET['id']; // Update $query = "update table set last_hit=NOW() where id='$id'"; $result = mysql_query($query); // Add if(the update failed) { $query = "insert into table (id,creation_date,last_hit) values ('$id',NOW(),NOW())"; $result = mysql_query($query); } What's the fastest way to check if the update failed? --PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Update or add?
Good advice , Thank you! -- Paul Nowosielski Webmaster On Friday 30 June 2006 15:45, Adam Zey wrote: > Paul Nowosielski wrote: > > On Friday 30 June 2006 14:37, Brian Dunning wrote: > >> I have a table where I want to update each record with today's date > >> as it's hit, or add the record if it's not in there: > >> > >> +--+-++ > >> > >> | id | creation_date | last_hit | > >> > >> +--+-++ > >> > >> I'm trying to do this with a minimum of hits to the db, so rather > >> than first searching to see if a matching record is in there, I > >> thought I'd just go ahead and update the matching record, check to > >> see if it failed, and if it failed then add a new one, like this: > >> > >> $id = $_GET['id']; > >> // Update > >> $query = "update table set last_hit=NOW() where id='$id'"; > >> $result = mysql_query($query); > >> // Add > >> if(the update failed) { > >>$query = "insert into table (id,creation_date,last_hit) values > >> ('$id',NOW(),NOW())"; > >>$result = mysql_query($query); > >> } > >> > >> What's the fastest way to check if the update failed? > > > > This is from the php.net docs. > > > > Return Values > > > > For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a > > resource on success, or FALSE on error. > > > > For other type of SQL statements, UPDATE, DELETE, DROP, etc, > > mysql_query() returns TRUE on success or FALSE on error. > > > > > > So if($result == 0){ > > do something; > > } > > No no no no no! Never like that! What you want is this: > > > if($result === false){ > do something; > } > > Never use 0 as a placeholder for false, and never use == to compare > boolean values. 0 is an integer, false is a boolean. Using === ensures > that result is false, and that it is a boolean that is false. It > compares the value AND the type. > > Regards, Adam. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Update or add?
Paul Nowosielski wrote: On Friday 30 June 2006 14:37, Brian Dunning wrote: I have a table where I want to update each record with today's date as it's hit, or add the record if it's not in there: +--+-++ | id | creation_date | last_hit | +--+-++ I'm trying to do this with a minimum of hits to the db, so rather than first searching to see if a matching record is in there, I thought I'd just go ahead and update the matching record, check to see if it failed, and if it failed then add a new one, like this: $id = $_GET['id']; // Update $query = "update table set last_hit=NOW() where id='$id'"; $result = mysql_query($query); // Add if(the update failed) { $query = "insert into table (id,creation_date,last_hit) values ('$id',NOW(),NOW())"; $result = mysql_query($query); } What's the fastest way to check if the update failed? This is from the php.net docs. Return Values For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error. For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error. So if($result == 0){ do something; } No no no no no! Never like that! What you want is this: if($result === false){ do something; } Never use 0 as a placeholder for false, and never use == to compare boolean values. 0 is an integer, false is a boolean. Using === ensures that result is false, and that it is a boolean that is false. It compares the value AND the type. Regards, Adam. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Update or add?
On Friday 30 June 2006 14:37, Brian Dunning wrote: > I have a table where I want to update each record with today's date > as it's hit, or add the record if it's not in there: > > +--+-++ > > | id | creation_date | last_hit | > > +--+-++ > > I'm trying to do this with a minimum of hits to the db, so rather > than first searching to see if a matching record is in there, I > thought I'd just go ahead and update the matching record, check to > see if it failed, and if it failed then add a new one, like this: > > $id = $_GET['id']; > // Update > $query = "update table set last_hit=NOW() where id='$id'"; > $result = mysql_query($query); > // Add > if(the update failed) { >$query = "insert into table (id,creation_date,last_hit) values > ('$id',NOW(),NOW())"; >$result = mysql_query($query); > } > > What's the fastest way to check if the update failed? This is from the php.net docs. Return Values For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error. For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error. So if($result == 0){ do something; } -- Paul Nowosielski Webmaster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Update or add?
> > I have a table where I want to update each record with today's date > as it's hit, or add the record if it's not in there: > > +--+-++ > | id | creation_date | last_hit | > +--+-++ > > I'm trying to do this with a minimum of hits to the db, so rather > than first searching to see if a matching record is in there, I > thought I'd just go ahead and update the matching record, check to > see if it failed, and if it failed then add a new one, like this: > > $id = $_GET['id']; > // Update > $query = "update table set last_hit=NOW() where id='$id'"; > $result = mysql_query($query); > // Add > if(the update failed) { >$query = "insert into table (id,creation_date,last_hit) values > ('$id',NOW(),NOW())"; >$result = mysql_query($query); > } > > What's the fastest way to check if the update failed? > Maybe you should be looking at using REPLACE instead of INSERT or UPDATE. REPLACE will update the record if it exists, OR add it if it doesn't. JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php