Stop insert on duplicate

Question

I have this code that insert email from array. The email column is set to unique so no duplication should happen.

$pieces = explode(",", $email);

if (!empty($pieces)){

    foreach ($pieces as $value) {
       mysqli_query($con,"INSERT INTO name (`email`, `email_id`) VALUES ('$value', '$id')");
    }

   $num = mysqli_affected_rows($con);

        if($num != 0){  
            $response = 1;  // all record inserted

        }
        if (mysqli_errno($con) == 1062) {
            $response = 0; // duplicate entry, ALL RECORD SHALL NOT INSERTED.

        }

}   
else{

        $response = 2; // empty string, no query

}     

        $display = array('response' => $response);
        echo json_encode($display);

What I want to do is to stop ALL insert after duplicate error code 1062 was found. The problem is some email in the array was not unique so insertion still happen. How to prevent insertion whether unique or non unique email was availabe in the array?

I understand I could SELECT and compare with $pieces array but I try to do only INSERT for now. Is it possible?

UPDATE : I actually try to use mysqli_affected_row and mysqli_errno($con) to set my own error code and give it to user. The problem is even if in the array got the unique email, insertion still happen, so that's make my error code useless, that's why I think I need to stop them all during insert.

This might be a bit obvious but your error checker never triggers before the loop is finished? — icecub, Oct 12 '14 at 05:01
http://blog.maverickgroups.com/mysql/mysql-handling-duplicates/ this answer your question? — Jhecht, Oct 12 '14 at 05:01
Maybe you should use SELECT + NOT IN ($email) to get emails that don't exist and only then insert it? In my opinion, it's quite strange idea to rely on error instead of check it yourself. P.S.: You forgot to assign resource id received from `mysqli_query`. — volter9, Oct 12 '14 at 05:09
If this is production code, from a security standpoint, this is a very bad idea. — bryonbean, Oct 12 '14 at 05:21
@bryonbean Please don't make pointless comments. Add why it's a bad idea, like probebly in this case no prepared statements. — icecub, Oct 12 '14 at 05:24
you should consider using transaction, if there is a duplicate, then don't commit. — ajreal, Oct 12 '14 at 05:31

score 1 · Answer 1 · answered Oct 12 '14 at 06:57

1

Check the response after each insert in your loop, rather than doing all the inserts and then checking the result (of the most recent function call).

answered Oct 12 '14 at 06:57

Alain Collins

16,268
2
32
55

score 0 · Answer 2 · answered Oct 12 '14 at 14:52

Here's is how I solved it. By using transaction (commit).

        $pieces = explode(",", $email);

        $total_pieces = count($pieces);

if (!empty($email)){
        //insert
    $total = 0;

    mysqli_autocommit($con, FALSE); //http://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli

    foreach ($pieces as $value) {
      //echo "$value <br>";
        $result = mysqli_query($con,"INSERT INTO name (`email`, `email_id`) VALUES ('$value', '$id')");

        if ($result){   
                $total = $total + count($value); // the total that succesfully insert not including duplicate.
        }

    }

    if ($total_pieces == $total){
        mysqli_commit($con); //INSERT INTO aren't auto committed because of the autocommit(FALSE)
        $response = 1;  //record updated
    }
    else{
        $response = 0; // duplicate record found!
    }


}   
else{

            $response = 2; // no record updated

}           

            $display = array('response' => $response);
            echo json_encode($display);

Thanks to everyone here that give me the idea and trying to help.

Stop insert on duplicate

2 Answers2