What is the best MySQL practice to insert a record if it does not already exist, and return its id? Imagine a simple 2-field table, with an auto-increment primary key (id) and a unique key VARCHAR field (name):
CREATE TABLE IF NOT EXISTS
Most of the results of a search for the optimal solution to this requirement are based on a INSERT . . . ON DUPLICATE KEY UPDATE approach:
Note the unique key VARCHAR field is as long as it can be (228) without returning the error "Specified key was too long; max key length is 1000 bytes".
Thanks Oleg. Yes, the logic you stated (again below) is what I was going for:
"if select (mysql_num_rows) >= 1 then select id else insert contact and mysql_insert_id()"
I was enquiring as to whether there is a preferred MySQL practice (e.g. using stored procedures or other approach) where a single query might suffice over a single MySQL connection, or whether the dual query approach (above) is always necessary to be implemented in another language - e.g. traditional approach using PHP:
if (isset($_POST['contact']) ? $_POST['contact'] : '')
$database = '';
$db_user = '';
$db_password = '';
$contact_safe = mysql_real_escape_string ($_POST['contact']);
$sql_select = "SELECT * FROM `contact` WHERE `name` LIKE '$contact_safe'";
$link = mysql_connect ('localhost', $db_user, $db_password ) or die(mysql_error());
mysql_select_db ($database, $link) or die(mysql_error());
$result_select = mysql_query ($sql_select) or die(mysql_error());
$count = mysql_num_rows ($result_select);
$id = $rows['id'];
$sql_insert = "INSERT INTO `contact` (`name`) VALUES ('$contact_safe')";
$result_insert = mysql_query ($sql_insert) or die(mysql_error());
$id = mysql_insert_id();
echo "some error occurred";
echo "The id for ".$contact_safe." is ".$id;
$html = "<form action = '' method = 'post'>
<input type = 'text' name = 'contact' />
<input type = 'submit' name = 'submit' value = 'Submit' />
Since the id column is auto-incremented and used as a PK of your table and name column is unique, you probably have to have a script which first checks the existence of the record with the specified name and if it does not then insert the record and return the value of the auto-incremented id, something like this:
Basically, you would want to always return something to the calling code, so if the record with the specified name already exists then you return -1. Otherwise, you insert a new record and select the value of the newly inserted id by calling the last_insert_id() function.
In any case, you don't want to pass the id as a parameter because the respective column is designed to auto-increment, so it will insert the correct value on auto-pilot. If you wish to return the value of id column for the record which already exists (rather than returning -1) then you can change the script and use something else in place of if exists. Something like this:
With this script, you will get back the id of already existing record (nothing inserted) or the id of newly inserted record after it has been inserted. Use this script if returning the id of already existing record is better then using - 1 for the same.