asked on 8/25/2016

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

PHP MySQL Server

Last Comment

Julian Hansen

8/26/2016

SOLUTION

Ray Paseur

8/25/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

stkoontz

8/25/2016

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

Julian Hansen

8/25/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Ray Paseur

8/25/2016

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.

stkoontz

8/26/2016

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

Ray Paseur

8/26/2016

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.

stkoontz

8/26/2016

ASKER

Thanks for the help from both of you!

Julian Hansen

8/26/2016

You are welcome.