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
– Iłya Bursov, Commented Nov 8, 2013 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. — Frantisek
– Frantisek, Commented Nov 8, 2013 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
– Iłya Bursov, Commented Nov 8, 2013 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
– a1ex07, Commented Nov 8, 2013 at 21:34
stackoverflow.com/questions/3164505/… this link may help you. — Rakibul Hasan
– Rakibul Hasan, Commented Apr 30, 2015 at 22:21

a1ex07 · Accepted Answer · 2013-11-08 21:33:09Z

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 8, 2013 at 21:33

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frantisek Over a year ago

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

a1ex07 Over a year ago

@Richard Rodriguez : check updated version, it should be good

Collectives™ on Stack Overflow

MySQL - "insert if not exists" query?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related