sql insert and update question

Question

I have a form which to insert data into a database. This form takes the content of the fields, and then displays the result page showing the entered information in context. There is a link on this page to edit the user info, which go back to the previous form. Obviously, I do not want duplicate records inserted. Is there an easy way to use an update statement if a record already exists? I am doing this with ajax and php.

Answer 1

MySQL supports the addition of ON DUPLICATE KEY UPDATE to an INSERT statement, which should do what you want.

Answer 2

Take a look at:

• INSERT ... ON DUPLICATE: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
• REPLACE INTO: http://dev.mysql.com/doc/refman/5.0/en/replace.html

INSERT ... ON DUPLICATE will allow you to issue an UPDATE query when a UNIQUE INDEX or PRIMARY KEY is matched.

REPLACE works exactly the same, but if the row is found, the old row is deleted prior to inserting a new one. When using cascading deletes, this is especially something to take into account!

Answer 3

Assuming you have a field like 'username' or 'email', you could make use of that field to check if a record already exists, if it does, update it.

$res = mysql_query("SELECT primary_key FROM my_table WHERE `email` = '" . mysql_real_escape_string($email) . "'");

if($row = mysql_fetch_array($res))
{
    // Record exists, update it
    $q = "UPDATE my_table SET `username` = '" . mysql_real_escap_string($username) . "' WHERE primary_key = " . (int) $row['primary_key'];
}
else
{
    // Record doesn't exist, insert
    $q = "INSERT INTO my_table(username, email) VALUES('" . mysql_real_escape_string($username) . "', '" . mysql_real_escape_string($email) . "');";
}

In the above example I assume you have a primary key field that's an integer (primary_key).

Answer 4

You should consider using an ORM like http://www.ezpdo.net/blog/?p=2 Plain SQL in web applications should only be used if absolutely neccessary, alone for security reason, but also to avoid problems like yours.