Solved

PHP PDO - Insert Ignore Still Adds Record

Posted on 2016-08-25
8
91 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 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 57

Accepted Solution

by:
Julian Hansen earned 250 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 110

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 110

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 57

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

691 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