stkoontz
asked on
PHP PDO - Insert Ignore Still Adds Record
I'd like to check if a record exists, if it doesn't, then add it. If it does, don't do anything. I found the 'insert ignore' command and tried it with this code.
Any ideas on a fix are greatly appreciated.
Steve
$query = $qry_contact_church_owner->prepare('INSERT IGNORE INTO contact_church_owner (
id_contact_church,
al_username
)
VALUES (
:id_contact_church,
:al_username
)');
$query->bindParam(':id_contact_church', $id_contact_church);
$query->bindParam(':al_username', $al_username, PDO::PARAM_INT);
When I run the code, the record is inserted even though there is already a record in the table.Any ideas on a fix are greatly appreciated.
Steve
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't quite know what I'm looking at with the screen shot of the table layout. Are any of the columns UNIQUE or AUTO_INCREMENT keys? If so, a match on any of these key columns should cause INSERT IGNORE to not insert a new row.
ASKER
I think I got it. In Navicat I clicked the "Indexes" tab and setup a Unique index on the 2 fields. Does the 'IGNORE' clause just tell the code to ignore the error from the attempt at adding unique data?
Steve
Steve
Yes, I think that about sums it up. INSERT which duplicates a UNIQUE column normally causes errno=1062 and does not do the duplicate insertion. INSERT IGNORE does not cause the error and does not do the duplicate insertion.
ASKER
Thanks for the help from both of you!
You are welcome.
ASKER
Steve
insert.jpg