Best MySQL practice to insert a record if it does not already exist, and return its id

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:

<?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());
if ($result_select)
{
$count = mysql_num_rows ($result_select);
if ($count>=1)
{
$rows=mysql_fetch_array($result_select);
$id = $rows['id'];
}
else
{
$sql_insert = "INSERT INTO `contact` (`name`) VALUES ('$contact_safe')";
$result_insert = mysql_query ($sql_insert) or die(mysql_error());
$id = mysql_insert_id();
}
}
else
{
echo "some error occurred";
}
mysql_close($link);
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' />
</form>";

echo $html;
?>

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:

if exists (select * from contact where name = @name) then
    select -1;
else
    insert into contact(name) values(@name);
    select last_insert_id();
end if;

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:

declare @id bigint(13);

set @id = (select id from contact where name = @name);

if (@id is null) then
    insert into contact(name) values(@name);
    set @id = (select last_insert_id());
end if;

select @id;

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.

Oleg