MySQL - insert if not already exists

Question

I want to execute this MySQL query:

INSERT INTO `cron-stats` (`user`) VALUES (".(int)$d['by-user'].")

Whenever such a user does not yet exist, as in:

 SELECT 1 FROM `cron-stats` WHERE `user` = ".(int)$d['by-user']." 

How can I execute this in a single request?

Answer 1

you can use ON DUPLICATE KEY UPDATE

 INSERT INTO `cron-stats` (`user`) VALUES ('yourValue') ON DUPLICATE KEY UPDATE user = user; 

• ENABLE DUPLICATE KEY UPDATE

but to execute the INSERT you need to set the UNIQUE index in the user column.

if the column does not already have index , follow the instruction below

  ALTER TABLE `cron-stats` ADD CONSTRAINT tb_un UNIQUE (`user`) 

Answer 2

A bit hacky, but if you use a SELECT table, not VALUES , you can do:

 INSERT INTO `cron-stats`(`user`) SELECT u FROM (SELECT @dByUser AS u) x WHERE NOT EXISTS(SELECT 1 FROM `cron-stats` WHERE `user` = @dByUser) 

SQL Fiddle Demo

Answer 3

You can try this using the if else condition

 $chk = mysql_query("select 'the username' FROM `cron-stats`"); $rs = mysql_fetch_array($chk); if($rs == "") { $ins = mysql_query("INSERT INTO `cron-stats` (`user`) VALUES ('.(int)$d['by-user'].')"); } else{ echo "Duplicate entry"; }