stop insert dublicate entery in my database

Question

we have use this below code to avoid to insert dublicate entery in our databse but in some case it's not working

  $device_id = $_REQUEST['device_id'];
$fcm_id = $_REQUEST['fcm_id'];
$model_number = $_REQUEST['model_number'];
$os_version = $_REQUEST['os_version'];
$app_version = $_REQUEST['app_version'];
$created = round(microtime(true) * 1000);

if(isset($device_id)){
    $exist = mysqli_query($con, "SELECT * FROM `user` WHERE `device_id`='$device_id'");
    if(mysqli_num_rows($exist)>0){
        mysqli_query($con, "UPDATE `user` SET `fcm_id`='$fcm_id', `topics`=0 WHERE `device_id`='$device_id'");
    }
    else{
        mysqli_query($con, "INSERT INTO `user` (`device_id`, `fcm_id`, `model_number`, `os_version`, `created`) VALUES ('".$device_id."', '".$fcm_id."', '".$model_number."', '".$os_version."', '".$created."')");
    }

Welcome grey clan! When asking a question you need to clearly outline your current situation. Saying that something is sometimes not working is completely meaningless to us. Describe your solution, the results you have and the results you would like to get. — Dharman, Jul 13 '19 at 14:21
To avoid dubious duplicates, resort to DB means like unique keys. There are DBs providing for "UPDATE or INSERT…"/["INSERT ON DUPLICATE KEY UPDATE"](https://stackoverflow.com/a/4205207)/UPSERT, with others just attempt insert and resort to UPDATE on *insert failed because of duplicate key*. — greybeard, Jul 13 '19 at 14:48
Possible duplicate of [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — greybeard, Jul 13 '19 at 14:50

nbk · Answer 1 · 2019-07-14T10:23:39.980

Try this

  $device_id = $_REQUEST['device_id'];
  $fcm_id = $_REQUEST['fcm_id'];
  $model_number = $_REQUEST['model_number'];
  $os_version = $_REQUEST['os_version'];
  $app_version = $_REQUEST['app_version'];
  $created = round(microtime(true) * 1000);

  if(isset($device_id)){
    $stmt = $con->prepare("INSERT IGNORE INTO `user` (`device_id`, `fcm_id`,
       `model_number`, `os_version`, `created`) VALUES (?, ?, ?,?,?)");
    $stmt->bind_param($device_id, $fcm_id, $model_number, $os_version, $created );
    if ($stmt->execute()) {
    }
    $stmt->close();
  }

Update: Add to your tabekl following index, But you should think about what you put in the index. because only when all 4 Columns are identical, no new entry will be crated.

    CREATE UNIQUE INDEX idx_insert_user 
    ON user (`device_id`, `fcm_id`,
       `model_number`, `os_version`);

the insert ignore into will not enter duplicates into the table

yes, this is because your $created keeps changing every millisecond. I uppdated my answer. you should keep the chnages i made anyway . — nbk, Jul 14 '19 at 10:16

score 1 · Answer 2 · edited Jul 13 '19 at 17:10

1

try with this

$device_id = $_REQUEST['device_id'];
$fcm_id = $_REQUEST['fcm_id']; 
$model_number = $_REQUEST['model_number'];
$os_version = $_REQUEST['os_version'];
$app_version = $_REQUEST['app_version'];
$created = round(microtime(true) * 1000);

if(isset($device_id))
{
    $qry="UPDATE `user` SET `fcm_id`='$fcm_id', `topics`=0 WHERE EXISTS (SELECT * FROM `user` WHERE `device_id`='$device_id')";
    if(!mysqli_query($con,$qry)){ mysqli_query($con, "INSERT INTO `user` (`device_id`, `fcm_id`, `model_number`, `os_version`, `created`) VALUES ('".$device_id."', '".$fcm_id."', '".$model_number."', '".$os_version."', '".$created."')");
    }

edited Jul 13 '19 at 17:10

Makyen

31,849
12
86
121

answered Jul 13 '19 at 16:26

Akhil Kannan

21
3

Links to your site which are not *directly* related to the question/answer are generally considered spam. They are not something that's done on Stack Overflow/Stack Exchange. Please see: [**What signifies "Good" self promotion?**](//meta.stackexchange.com/q/182212), [some tips and advice about self-promotion](/help/promotion), [What is the exact definition of "spam" for Stack Overflow?](//meta.stackoverflow.com/q/260638), and [What makes something spam](//meta.stackexchange.com/a/58035). – Makyen Jul 13 '19 at 17:12

score 0 · Answer 3 · answered Jul 14 '19 at 10:36

0

For stoping the duplicate data on row from mysql database on insert query: MySQL INSERT IGNORE statement

For update case you need to put your condition for duplicate handling.

answered Jul 14 '19 at 10:36

Pankaj Chauhan

1,623
14
12

stop insert dublicate entery in my database

3 Answers3