MySQL - "insert if not exists" query?

Question

I'm trying to write a MySQL query which will update a blog post view counter if the user has not visited the post in the last 24 hours. I'm trying to write something like this (but this does not work):

  IF EXISTS (
     SELECT 1
     FROM `posts-views`
     WHERE
        `ip` = '".$_SERVER['REMOTE_ADDR']."'
        AND
        `time` > ".($time-60*60*24)."
        AND
        `post` = $id
  ) THEN
  NULL

  ELSE

  INSERT INTO `posts-views`
  (`post`, `time`, `ip`, `user`)
  VALUES
  ($id, $time, '".$_SERVER['REMOTE_ADDR']."', $me)

What's the correct way to fix the query?

you can use `insert ignore` - it will insert or just pass without error if row with duplicate keys exists, also it has nice feature `on duplicate key update` — Iłya Bursov, Nov 08 '13 at 21:30
I have no idea how to use INSERT IGNORE in this case. I'm not inserting the same data. The time may differ. — Richard Rodriguez, Nov 08 '13 at 21:32
you can, if you add new column with only date part (user will be able to vote 1 time per day) — Iłya Bursov, Nov 08 '13 at 21:34
`Insert ignore` requires unique key, which I don't think is possible to create in this case because of condition on `time` : `time > ....` . — a1ex07, Nov 08 '13 at 21:34
http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table this link may help you. — Rakibul Hasan, Apr 30 '15 at 22:21

score 2 · Accepted Answer · answered Nov 08 '13 at 21:33

2

From what I see you cannot use INSERT IGNORE in that case, but something like following should do the job :

  INSERT INTO `posts-views`
  (`post`, `time`, `ip`, `user`)
  SELECT $id, $time, '".$_SERVER['REMOTE_ADDR']."', $me 
  FROM dual 
  WHERE NOT EXISTS (
     SELECT 1
     FROM `posts-views`
     WHERE
    `ip` = '".$_SERVER['REMOTE_ADDR']."'
      AND
    `time` > ".$time-60*60*24."
    AND
    `post` = $id
  )

I completely omit escaping variables which definitely should be done in real code.

UPDATED - added from dual to avoid syntax error

answered Nov 08 '13 at 21:33

a1ex07

35,290
12
78
96

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT 1 FROM `posts-views` WHERE ' at line 6 – Richard Rodriguez Nov 08 '13 at 21:35
@Richard Rodriguez : check updated version, it should be good – a1ex07 Nov 08 '13 at 21:38

MySQL - "insert if not exists" query?

1 Answers1

Linked