phpmysqlinsertezsql

Check if a record exists and return its ID if it was found or created?

Possible Duplicate:
Normalized DB insertions

Is it possible to do a single query that will look for a record and either return it's ID or insert it and return it's ID? Here's what I was thinking may work for this:

INSERT INTO firstname (id, name) VALUES (NULL,'$name') 
ON DUPLICATE KEY UPDATE name ='$name', id=LAST_INSERT_ID(id)

If it helps, I'm using ezSQL and could just do a $db->query("..."); then an $id = $db->insert_id; to grab the insert id and use it in a variable.

Solution

  • You can write your own stored procedure which will check for existing record.

    1. Try to retrieve the record by its id
    2. Check if it is null, insert it
    3. Otherwise you got the row