Geek asks and answers

PHP MYSQL PDO & # 8594; Fatal error 23000, although there is a special procedure

I have a table in my db. My table has several fields, including a field with an automatically increasing identifier specified as a primary key, and another field called a link, which I set as unique. To populate this table, I have a php script that inserts records into this table using pdo. Each time the insert was successful (this means that the “link” does not exist in the table), I am incrementing a variable called $ newOnes. If the value of 'reference' is already in the table, an exception is thrown with code 23000. In this case, I am incrementing another variable called $ doublons. Unfortunately, my script runs a fatal error with an exception of 23000 when the while loop processes the last table entry. And I do not understand. Thanks in advance for your help. Greetings. Mark.

My php code is:

try {
  $connexion = connexion('localhost', 'user', 'user', 'mydb');
  $connexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $qry_bat = $connexion->query('SELECT...');
  $ins_db = $connexion->prepare('INSERT...');
}
catch (PDOException $e) {
  echo $e->getMessage();
}

while($row = $qry_bat->fetch(PDO::FETCH_ASSOC)) {
  try {
    $ins_db->execute(array(...));
    $newOnes++;
  }
  catch (PDOException $e) {
    if ($e->getCode() != 23000) {
      echo '<span class="msg-alert">'.$e->getMessage().'</span>';
    } else {
      $doublons++;
    }
  }
}

Fatal error I get (note that line 22 refers to while (...) line):

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]:  
Integrity constraint violation: 1062 Duplicate entry 'theFieldContentOfTheLastRecordOfTheTable' for key 'theFieldNameReference' in  
/myFilePath/file.php:22 Stack trace: #0 /myFilePath/file.php(22): PDOStatement->fetch(2)  
#1 {main} thrown in /myFilePath/file.php on line 22

EDIT //////////

original table (things to mention):
auto incremented id

table for insertion (things worth mentioning):
auto added to id field UNIQUE INDEX by reference

+3
source share
3 answers

(Update to answer)

Looks like this mistake , which is still open after almost five years; try instead:

while (true) {
  try {
    $row = $qry_bat->fetch(PDO::FETCH_ASSOC);
    if (!$row) break;
    $ins_db->execute(array(...));
    $newOnes++;
  }
  catch (PDOException $e) {
    if ($e->getCode() != 23000) {
      echo '<span class="msg-alert">'.$e->getMessage().'</span>';
    } else {
      $doublons++;
    }
  }
}
+6
source

The stack trace shows an exception that occurs in a selection that is outside of try / catch blocks.

The SELECT statement runs:

Integrity constraint violation: 1062 Duplicate entry 'theFieldContentOfTheLastRecordOfTheTable'

theFieldContentOfTheLastRecordOfTheTable, .

, , ?

+1

Look for dirty / duplicate data ALREADY in this column. Some applications allow you to use the "free" restriction AFTER the data is already captured. This may happen in your case.

0
source

More articles:


All Articles