Solved

PHP PDO - Insert Ignore Still Adds Record

Posted on 2016-08-25
8
40 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 108

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 51

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
 
LVL 108

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 108

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 51

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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 dynamically set the form action using jQuery.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now