MYSQL if insert data does not exist

Question

Guys I get duplicate records about insertion into my database for some reason with this code

$qry = "INSERT IGNORE INTO reports (". implode(", ",array_keys($reports)) .") VALUES (". implode(", ",array_values($reports)) .");"; if(!mysql_query(trim($qry), $link)) { die('Error: ' . mysql_error()); } 

The actual statement is

 INSERT IGNORE INTO reports (`inspection_number`, `report_date`, `customer`) VALUES ('996', '10-21-2012', 'Jd Daniel'); 

Now DB looks like

 19 NULL NULL NULL 996 NULL 0000-00-00 NULL Jd Daniel NULL NULL NULL NULL 20 NULL NULL NULL 996 NULL 0000-00-00 NULL Jd Daniel NULL NULL NULL NULL 21 NULL NULL NULL 996 NULL 0000-00-00 NULL Jd Daniel NULL NULL NULL NULL 22 NULL NULL NULL 996 NULL 0000-00-00 NULL Jd Daniel NULL NULL NULL NULL 

I thought INSERT IGNORE should have ignored duplicates? What?

EDIT Here is my table structure, I tried to use inspection_number as my unique index for comparison.

 -- -- Table structure for table `reports` -- DROP TABLE IF EXISTS `reports`; CREATE TABLE `reports` ( `key` INT UNSIGNED AUTO_INCREMENT, `role` VARCHAR(70), `region` VARCHAR(70), `inspection_type` VARCHAR(70), `inspection_number` VARCHAR(70), `customer_number` VARCHAR(70), `report_date` DATE DEFAULT NULL, -- Date field? Needs DATETIME instead? Needs DEFAULT NULL? -- Does this need to be created on upload, -- or is it uploaded from tablet? `order_date` DATE DEFAULT NULL, -- Date field? Needs DATETIME instead? Needs DEFAULT NULL? -- Ditto `customer` VARCHAR(70), `customer_division` VARCHAR(70), `location` VARCHAR(70), `memo` VARCHAR(255), -- Same as _comments? VARCHAR(255)?? `billing_key` VARCHAR(70), PRIMARY KEY(`key`) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; 

Answer 1

INSERT IGNORE will try to insert a record into the table and ignore the duplicate error from the database engine so that your script cannot continue.

To avoid duplication of data in the table. you need to create a PRIMARY KEY on your table. In this example below, more than one row with the same control_folder number will not be allowed.

Example:

  CREATE TABLE reports (
   inspection_number int (10) NOT NULL,
   report_date timestamp,
   customer VARCHAR (50),
   PRIMARY KEY (inspection_number)
 );

Answer 2

If you do not want to duplicate, you can use the Replace command instead of Insert.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

This will try to add the value if detecting a duplicate (from the primary key) removes the old one and inserts it again.

Answer 3

INSERT IGNORE considers a duplicate row if it has the same primary key or unique index. Most likely, none of the three fields ( inspection_number , report_date or customer ) are primary keys or unique indexes.