MYSQL if not exists insert data

Question

Guys I'm getting duplicate records on insert 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 this

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

DB now 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 that INSERT IGNORE was supposed to ignore duplicates? What's up?

EDIT Here's my table structure, I was trying to use inspection_number as my unique index to compare against.

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

The INSERT IGNORE will try to insert the record into the table, and ignores the duplicate error from the database engine so that your script won't fail to continue.

In order to avoid having duplicate data in your table. you need to create a PRIMARY KEY on your table. This example below will not allow more than 1 row with the same inspection_number number

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 any 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 detect a duplicate (from primary key) will delete the old one and insert it again.