I'm running a little method in CodeIgniter to insert some lines in the database (same table). I would like to see which insertion have failed inside a transaction (by returning an array of titles). My code is :
$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database
$this->ajout_ressource($ressourceCsv); //method to insert (basically, just an insert with active record)
if (($this->db->_error_message())!=null) {
$failure[] = $ressourceCsv['title'];
}
}
$this->db->trans_complete();
return $failure;
The fact is that if I don't make it a transaction (no $this->db->trans_...), it works perfectly and I have an array containing a few titles. But with the transaction, the array contains every titles since the first error. Is there a way to just get the title from the insertion which caused the transaction to rollback?
I have also tried with :
$failure = array(); //the array where we store what failed
$this->db->trans_start();
foreach ($data as $ressourceCsv){ //data is an array of arrays to feed the database
if (!$this->ajout_ressource($ressourceCsv)) { //active record insertion return true
$failure[] = $ressourceCsv['title']; // if successful
}
}
$this->db->trans_complete();
return $failure;
I believe that once an error occurs inside a transaction, you must rollback before any more DB mods can be made. That would explain the behavior you are seeing. After the first error, the transaction is "aborted" and you continue your loop, causing every subsequent SQL command to fail as well. This can be illustrated as follows:
Note it seems that "commit" does a "rollback" if there was an error (it was not a typo.)
Also BTW: use
$this->db->trans_status() === FALSE
to check for an error during the transaction.Update: Here's some (untested) code to do it in a transaction so that the inserts are not seen by others until you are ready: