Solved

PHP PDO - Insert Ignore Still Adds Record

Posted on 2016-08-25
8
75 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WordPress  Failed to Import Media 8 64
How can I split a variable 19 45
PHP and accessing Array Elements 3 33
Position image fpdf 4 16
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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.
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.

737 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