Ask a Question Register Login

php - Stop insert on duplicate

467

I have this code that insert email from array. The email column is set tounique 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 stopALL insert after duplicate error code1062 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 couldSELECT and compare with$pieces array but I try to do onlyINSERT for now. Is it possible?

UPDATE : I actually try to usemysqli_affected_row andmysqli_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.

951

Answer

Solution:

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).

531

Answer

Solution:

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.

People are also looking for solutions to the problem: java - Facebook 'code' parameter as part of query string for OAuth authentication

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.