Insert record if not exist without Unique key

Question

I have this code, but sometimes it's inserting me double records. I cannot set a unique key for field user_subid.

$sql="SELECT `user_subid` FROM `clicks` WHERE `user_subid`='".$user_subid."'";
$query=mysql_query($sql);
if(mysql_num_rows($query)==0){
    $sqlIns="INSERT INTO `clicks` (`file_id`, `country`, `date`, `timeClick`, `user_subid`) VALUES ('".$fileid."', '".$country."', '".$date."', '".$timeClick."', '".$user_subid."')";
    if(mysql_query($sqlIns)==true){
        echo 1;
    }
    else { echo 0;}
}

I tried with:

INSERT INTO `clicks` (
`id` ,
`file_id` ,
`country` ,
`date` ,
`timeClick` ,
`user_subid`
)
VALUES (
NULL , '1500', 'Canada', '2014-05-24', '1400934475', '7c282b9227328778680d5826e01f6d'
) WHERE NOT EXISTS (SELECT * FROM `clicks` WHERE `user_subid`='7c282b9227328778680d5826e01f6d');

But it's not working.

echo_Me · Accepted Answer · 2014-05-24T14:47:09.603

you looking for this ?

INSERT IGNORE INTO clicks ( id , file_id , country , date , timeClick , user_subid ) VALUES ( NULL , '1500', 'Canada', '2014-05-24', '1400934475', '7c282b9227328778680d5826e01f6d' )

EDIT:

 INSERT INTO `clicks` (
  `id` ,
  `file_id` ,
  `country` ,
  `date` ,
  `timeClick` ,
  `user_subid`
 )
 SELECT `id` ,
`file_id` ,
`country` ,
`date` ,
`timeClick` ,
`user_subid`
FROM  
 (SELECT NULL id , '1500' `file_id` , 'Canada' `country` , '2014-05-24' `date` , '1400934475' `timeClick` , '7c282b9227328778680d5826e01f6d' `user_subid`) a 
LEFT JOIN `clicks` b ON 
  a.id = b.id AND a.`file_id` = b.`file_id` AND a.`user_subid` = b.`user_subid` 
WHERE b.`user_subid` IS NULL;

or with NOT EXIST

  INSERT INTO `clicks` (
  `id` ,
  `file_id` ,
  `country` ,
  `date` ,
  `timeClick` ,
  `user_subid`
 )
 SELECT `id` ,
`file_id` ,
`country` ,
`date` ,
`timeClick` ,
`user_subid`
FROM  
 (SELECT NULL id , '1500' `file_id` , 'Canada' `country` , '2014-05-24' `date` , '1400934475' `timeClick` , '7c282b9227328778680d5826e01f6d' `user_subid`) a
 WHERE NOT EXISTS (
   SELECT 1 
   FROM `clicks` 
   WHERE `user_subid` = '7c282b9227328778680d5826e01f6d')

No, I tried with your code, but it's still inserting me. – Daniel Dulić May 24 '14 at 14:31 — Daniel Dulić, May 24 '14 at 14:31

score 1 · Answer 2 · answered May 24 '14 at 14:34

1

Is your SQL is working?

If you want update recode if recode is exist. Otherwise insert new recode. Just try UPSERT CODE. INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

answered May 24 '14 at 14:34

Dinuka Thilanga

3,990
9
51
89

use your primary key. table should have primary key. – Dinuka Thilanga May 24 '14 at 14:43

manout · Answer 3 · 2014-05-24T15:00:51.060

0

have you try to change

$sql="SELECT `user_subid` FROM `clicks` WHERE `user_subid`='".$user_subid."'";
$query=mysql_query($sql);
if(mysql_num_rows($query) < 1){ // change == 0 to < 1
$sqlIns="INSERT INTO `clicks` (`file_id`, `country`, `date`, `timeClick`, `user_subid`)   VALUES ('".$fileid."', '".$country."', '".$date."', '".$timeClick."', '".$user_subid."')";
if(mysql_query($sqlIns)==true){
    echo 1;
}
   else { echo 0;}
}

edited May 24 '14 at 15:00

answered May 24 '14 at 14:39

manout

41
1
10

I need to insert only if not exist. For unique clicks. But i Cannot set Unique Key. – Daniel Dulić May 24 '14 at 14:42

Insert record if not exist without Unique key

3 Answers3

Linked