Link to home
Start Free TrialLog in
Avatar of stkoontz
stkoontzFlag for United States of America

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.
	$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);

Open in new window

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stkoontz

ASKER

I'm using Navicat for MySQL to create the tables, so I don't have a CREATE TABLE statement.  Attached is a screen shot of the table layout if that helps.

Steve
insert.jpg
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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.
Thanks for the help from both of you!
You are welcome.