MySQL: INSERT if name doesn't exist

[erikcw]

Hi all,

I am trying to write a query that will insert if a row with the same name (col='name') doesn't alread exist.

So basically, if the query trys to insert Jim, but Jim is already in the table, it will skip the operation.

Can anyone tell me how to do this?

Also - is it more efficiont to run one query with multiple inserts or multiple queries with one insert.

Thanks!
Erik

[arran]

Hi Erik,

In > 4.0.1 you can use

INSERT INTO IGNORE

which ignores any inserts causing duplicate-key violations. Just make sure the key is chosen correctly and you will have the behaviour you require.

Is it more efficient to run one query with multiple inserts or multiple queries with one insert.

The former - generally speaking the less database calls the better.

arran.

[dkin]

This is what I use

$sql = mysql_query("SELECT colname FROM table WHERE colname = '$var'") or die ("query 1: " . mysql_error());
$mysql_num = mysql_num_rows($sql);

if ($mysql_num >= 1)
{
echo 'Cannot insert duplicates';
}
else
{
$echo 'query returns no results, feel free to insert the row.';

##Run insert query.
}

Hope that helps.

Dylan