I am working in PHP.
Please what's the proper way of inserting new records into the DB, which has unique field. I am inserting lot of records in a batch and I just want the new ones to be inserted and I don't want any error for the duplicate entry.
Is there only way to first make a SELECT and to see if the entry is already there before the INSERT - and to INSERT only when SELECT returns no records? I hope not.
I would like to somehow tell MySQL to ignore these inserts without any error.
Thank you
You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);
This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Try creating a duplicate table, preferably a temporary table, without the unique constraint and do your bulk load into that table. Then select only the unique (DISTINCT) items from the temporary table and insert into the target table.
You can use triggers.
Also check this introduction guide to triggers.
$duplicate_query=mysql_query("SELECT * FROM student") or die(mysql_error());
$duplicate=mysql_num_rows($duplicate_query);
if($duplicate==0)
{
while($value=mysql_fetch_array($duplicate_query)
{
if(($value['name']==$name)&& ($value['email']==$email)&& ($value['mobile']==$mobile)&& ($value['resume']==$resume))
{
echo $query="INSERT INTO student(name,email,mobile,resume)VALUES('$name','$email','$mobile','$resume')";
$res=mysql_query($query);
if($query)
{
echo "Success";
}
else
{
echo "Error";
}
else
{
echo "Duplicate Entry";
}
}
}
}
else
{
echo "Records Already Exixts";
}
Source: Stackoverflow.com