Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PHP PDO - Insert Ignore Still Adds Record

Posted on 2016-08-25
8
Medium Priority
?
176 Views
Last Modified: 2016-08-26
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
0
Comment
Question by:stkoontz
  • 3
  • 3
  • 2
8 Comments
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 41770847
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.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 41770866
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
0
 
LVL 61

Accepted Solution

by:
Julian Hansen earned 1000 total points
ID: 41770952
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41770993
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.
0
 
LVL 2

Author Comment

by:stkoontz
ID: 41771689
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
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41771699
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.
0
 
LVL 2

Author Closing Comment

by:stkoontz
ID: 41771707
Thanks for the help from both of you!
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 41771710
You are welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question