INSERT INTO query only if record doesn't already exist

In my table I have two fields: v_id and ip_address. I want to insert some data into this table only if the IP address doesn't already exist.

After Google'ing I came across the INSERT IGNORE INTO statement, and this is my code:

开发者_JAVA百科public function update_visits($ip_address)
{
    $sql = 'INSERT IGNORE INTO `24h_visits` (ip_address) VALUES (?)';
    if($this->db->query($sql, array($ip_address)))
    {
        return TRUE;
    }
    return FALSE;
}

It runs fine and without errors, but duplicate rows are still being made, even if the same IP is passed in as a parameter.

Anyone got a clue? Thanks.

You have to create a UNIQUE index on ip_address for INSERT IGNORE to work:

ALTER TABLE 24h_visits
    ADD UNIQUE INDEX(ip_address)

However, I haven't seen the entirety of your schema, but I would assume that there's a column that stores a timestamp of the last visit. It's the only way this would make sense (so you can purge visits older than 24 hours every now and then).

In this case, you actually don't want INSERT IGNORE, but INSERT ... ON DUPLICATE KEY UPDATE instead. Assuming you have a column called last_visit:

INSERT INTO 24h_visits (ip_address, last_visit)
   VALUES ('$ip_address', NOW())
   ON DUPLICATE KEY UPDATE last_visit = NOW();

With INSERT IGNORE, the new row is never inserted, and thus you would always have the first value ever inserted on last_visit, which (the way I see it) is not entirely correct.

Add the UNIQUE constraint to your ip_address column.

Then your query would fail if it attempts to add a duplicate ip_address row (unless you use INSERT IGNORE).

The other answers don't actually answer the question: Creating a unique index prevents the duplicate from being inserted, which is a good idea, but it doesn't answer "how to insert if not already there".
This is how you do it:

INSERT IGNORE INTO 24h_visits (ip_address)
select ?
where not exists (select * from 24h_visits where ip_address = ?)

Additionally, this approach does not require any changes to schema.