CODEWITHSUNDEEP
phpmysqlerror-handling
Heena Shah
Heena Shah

Reputation: 143

Does mysql error in inserting unique record terminate foreach loop on first error?

I am inserting mySQL result into table which has unique index on account number and trasaction date

foreach ($fulldata as $partdata){
       $name=$partdata['name'];
       $acno=$partdata['acno'];
       $trdate=$partdata['trdate'];
       $sql="INSERT into mytable (name,acno,trdate) values ('$name','$acno','$trdate')";
       $result=mysqli_query($con,$sql) or die(mysqli_error($con));
}

If the entry is duplicate, the error is shown 'duplicate entry on...' and the loop is terminated. It does not take up next record for processing even if it does not have duplicate values with existing records in the database.

Ideally, it should show error for only those rows which has duplicate entry but insert all those records which are unique new entries. Can any other type of loop help ?

Upvotes: 0

Views: 485

Answers (2)

Someone Special
Someone Special

Reputation: 13588

I would use a try/catch approach to echo out the statements. But I use PDO.

foreach ($fulldata as $partdata){
       $name=$partdata['name'];
       $acno=$partdata['acno'];
       $trdate=$partdata['trdate'];
       $sql="INSERT into mytable (name,acno,trdate) values (?,?,?)";

       try {

            $query = $db->prepare($sql);
            $query->execute(array($name, $acno, $trdate));

       } catch (PDOException $e) {
            echo $e->getMessage();
            echo 'Fail to execute following statement: ' . $sql ."\r\n";    
      }    
}

Now I will also which insert statement fails.

Upvotes: 1

Yan.Zero
Yan.Zero

Reputation: 449

You can use INSERT ... ON DUPLICATE KEY UPDATE:

  INSERT INTO mytable (name,acno,trdate) VALUES ('$name','$acno','$trdate')
  ON DUPLICATE KEY UPDATE name=name;

or INSERT IGNORE INTO:

  INSERT IGNORE INTO mytable (name,acno,trdate) VALUES ('$name','$acno','$trdate')

Upvotes: 2

Related Questions