PHP PDO - Insert Ignore Still Adds Record

stkoontz
stkoontz used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016
Commented:
As I understand it, INSERT IGNORE will only skip insertion if there is a match on the key in columns with PRIMARY KEY or UNIQUE designations.  Can you please post the CREATE TABLE statement?  Thanks.

Author

Commented:
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
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
You can get the table definition like so
SHOW CREATE TABLE contact_church_owner;

Open in new window


and Ray is correct - the INSERT IGNORE only works if you have a unique index defined - otherwise there is no way for the database to realise there is a duplicate.

The other options are REPLACE - not recommended as this will do a delete and an insert

The third is ON DUPLICATE KEY - this gives you the option to update values in the matched record if you so wish.
$query = <<< QUERY 
  INSERT INTO contact_church_owner (id_contact_church, al_username)
  VALUES(:id_contact_church, :al_username ) 
  ON DUPLICATE KEY SET id_contact_church = :id_contact_church
QUERY;

Open in new window

The above code will do nothing on a duplicate key - it will simply attempt to set the id_contact_church equal to the new id_contact_church which (if it is the primary key) will be a duplicate and result in no action. However, if you did want to update say a counter to count how many times a duplicate came in you could do it with the above statement.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2016

Commented:
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.

Author

Commented:
Thanks for the help from both of you!
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial