mySQL update/insert

[Nedals]

I am attempting something similar to the following:

 

mysql_query("UPDATE table SET name='$name' WHERE id=$id");
if (mysql_affected_rows()==0) {
mysql_query("INSERT INTO table (id, name) VALUES ('$name',$id);
}

 

If the $id row does not exists in the table, mysql_affected_rows() returns 0 and a new $id row gets inserted

but if the $id row already exists and UPDATE changes nothing, mysql_affected_rows() still returns 0

and gives an 'duplicate id' error as expected.

 

I know I could use a SELECT to test for the existance of the $id row.

In Perl there is an '0E0', 'zero but true', condition to handle this. Is there an equivilant in PHP?

 

 

[PFMaBiSmAd]

You would want to use an INSERT ... ON DUPLICATE KEY UPDATE ... query -

 

INSERT INTO tbl_name [(col_name,...)]

    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

    ON DUPLICATE KEY UPDATE

      col_name=expr

        [, col_name=expr] ... ]

 

 

[Nedals]

I was not familiar with the ON DUPLICATE.

Tried it out and its works perfectly.

 

Thanks