I have code similar to the following:
$sql2 = "ALTER TABLE table ADD UNIQUE(field2, feild3, field4, field5);";
$sql2 .= "INSERT IGNORE INTO table (field1, field2, feild3, field4, field5) VALUES ('$_POST[field1]','$_POST[field2]','$_POST[field3]','$_POST[field4]','$_POST[field5]')";
if (!mysql_query($sql2,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
}
I have found that I get an error if the table already has two rows which are the same but works well otherwise. I am worried that if I add the duplicates from two different sources that they will both be added to the table and later requests will trigger the error I got earlier because of the two almost identical rows. Is it possible to add two almost identical rows as shown above if the php file is executed at the same time from two different sources?
EDIT: FYI The code is a snippet from a larger program. I am sending data from an android app I am working on to the $_POST fields
mysql_query DOES NOT ALLOW execute two queries in single call
insert ignore won't return error when duplicate found (that's why it named as ignore)
So, the FIRST error is caused by the alter ...; insert ...
Then alter table is require execute once only,
on your next execution, you are no longer require to perform alter table (again).
Otherwise, you will getting the duplicate INDEX error (trying to add an existing index)
For the unique index, is meant to prevent duplicate,
so, it only will have one unique record (no matter what)
And for you funny almost identical,
it will never happen
it blocked when duplication found on field 2..5
What does the table DDL look like? The way to control duplicates is using keys on your table that allow/disallow records based on that key structure. There is no such thing in SQL as "almost identical" they are either duplicates or unique.
When working with Db's and updates you should always build the update/delete/modify constraints within the DDL. Therefore it should not matter what or how many processes are trying to modify the DB.. The DB will manage the updates using table locks etc.. and will manage the updates to the table based on your constraints.
If you have more questions or this did not help, please post you table DDL.
There is no such thing as "almost identical rows". Rows are either identical or not. If you are referring to your UNIQUE criteria, again rows are either unique based on the criterial specified or not.
At the time the INSERT command is being executed, if there is already a UNIQUE constraint in place by the same PHP script or another PHP script, and the row being inserted violates the UNIQUE constraint, the second query will be rejected even if it's a fraction of seconds after the first query. If they are not identical, doesn't matter who asks for the query or how many pages are involved, it goes through.