1

Possible Duplicate:
How to ‘insert if not exists’ in MySQL?

I have a few inserts and I only want them to actually insert if there isn't already the same information in there.

I only want the insert to run using WHERE NOT EXISTS, would anybody be able to help me adding that to my queries?

Queries:

$db->Query("INSERT INTO `surfed` (site) VALUES('".$id."')");

Thanks so much!

Community
  • 1
  • 1
CustomNet
  • 691
  • 3
  • 10
  • 30

3 Answers3

4

Use the IGNOREkeyword

INSERT IGNORE INTO surfed (user, site) VALUES('".$data['id']."', '".$id."')

If you have a unique key constraint on the user-site combination then it would normally generate an error on duplicates and abort the insertion. Using ignore in the insert statement will ignore the error and just not insert anything.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. ... With IGNORE, the row still is not inserted, but no error is issued.

See here for more infos about it

SQLFiddle example

juergen d
  • 186,950
  • 30
  • 261
  • 325
  • So by using the code you supplied it will only submit in to the database if it doesn't already exist and not produce any errors? – CustomNet Oct 29 '12 at 16:40
  • Yes. If you have a unique key constraint on the `user`-`site` combination. – juergen d Oct 29 '12 at 16:42
  • This doesn't seem to be working, I have removed the INSERT in to user because I only want site to be submitted. So now it currently looks like: $db->Query("INSERT IGNORE INTO `surfed` (site) VALUES('".$id."')"); + $db->Query("INSERT IGNORE INTO `surfed` (site) VALUES('".$sit['id']."')"); Basically lots of people view lots of different sites but I only want a certain site to be submitted once in to the site column. – CustomNet Oct 29 '12 at 16:51
  • Do you have a unique key on `site`? If so, then the insert should fail and not return an error. – juergen d Oct 29 '12 at 17:09
  • See [this SQLFiddle](http://sqlfiddle.com/#!2/79bf4/1) – juergen d Oct 29 '12 at 17:13
0

First your two columns need to be a combined key, then you can use INSERT IGNORE as outlined by juergen d or use

ON DUPLICATE KEY UPDATE user=user

See similar question here: INSERT ... ON DUPLICATE KEY (do nothing)

For the revised question of only having a site column, make sure the site column is a unique key or primary key and then use 

$db->Query("INSERT INTO `surfed` (site) VALUES('".$id."') ON DUPLICATE KEY UPDATE site=site");
Community
  • 1
  • 1
sreimer
  • 4,706
  • 2
  • 30
  • 41
  • Hey, thanks for the replies but I have removed the users column, I only want the site to be submitted. If the site is already submitted then I don't want it to be submitted again. Would you be able to show me how to do this? – CustomNet Oct 29 '12 at 17:01
0

You should be able to go with mysql_num_rows. If that returns a value of 0, then there is nothing in your table matching what you want to insert. (WHERE NOT EXISTS). So make something like "if mysql_num_rows = 0 then insert else do nothing".

ckarlss0n
  • 23
  • 4