PHP: How can I check for errors during mysqli compilation?

Question

I insert many records using the mysqli commit statement using this code from http://www.php.net/manual/en/mysqli.commit.php#88857 . Then I check the affected lines using: $ Mysqli-> affected_rows but even if all the records have been inserted, I get zero lines.

How can I check when commit failed and get an error? Thanks you

Answer 1

You can do something like this:

mysqli_autocommit($dbconn, FALSE); $errors = array(); if (!$mysqli->query(/* some SQL query */)) { $errors[] = $mysqli->error; } // ... more queries like the above if(count($errors) === 0) { $mysqli->commit() } else { $mysqli->rollback(); print_r($errors); } 

When the request goes wrong, it will add an error to the $ errors array so you know what went wrong. You can also add keys with identifiers for requests so that you know which request went wrong.

For better handling, you can write the UnitOfWork class:

 class UnitOfWork { protected $_db; protected $_errors; protected $_queries; public function __construct($db) { $this->_db = $db; $this->_errors = array(); $this->_queries = array(); } public function addQuery($id, $sql) { $this->_queries[$id] = $sql; return $this; } public function getErrors() { return $this->_errors; } public function try() { $this->_db->autocommit($this->_db, FALSE); foreach($this->_queries as $id => $query) { if ($this->_db->query($query) === FALSE) { $this->_errors[$id] = $this->_db->error; } } $hasErrors = count($this->_errors); ($hasErrors) ? $this->_db->rollback() : $this->_db->commit(); $this->_db->autocommit($this->_db, TRUE); return !$hasErrors; // return true on success } } 

and you can use it as

 $unit = new UnitOfWork($mysqli); $unit->addQuery('foo', 'SELECT foo FROM somewhere') ->addQuery('bar', 'SELECT bar FROM somewhereElse') ->addQuery('baz', 'SELECT baz WITH brokenQuery'); if($unit->try() === FALSE) { print_r($unit->getErrors()); } 

Answer 2

mysqli::affected_rows will return the number of rows affected by the last MySQL operation.

If you do something like this (pseudocode):

 $db->query("insert ..."); $db->query("insert ..."); $db->query("insert ..."); $db->commit(); $num = $db->affected_rows(); 

You will not get the number of rows inserted: the commit statement is the last executed, and it does not affect any row.

If you want to know whether mysqli::commit succedeed or not, you should check its return value (quoting):

Returns TRUE on success or FALSE on failure.

If he returned TRUE , all your previous inserts, starting from the beginning of the current transaction, will be committed.

And if an error occurs, you can use mysqli::errno and / or mysqli::error to get information about it.