PHP: How can I check for errors during a mysqli commit?

Question

I am inserting a lot of records using mysqli commit statement, using this code from http://www.php.net/manual/en/mysqli.commit.php#88857. I then check affected rows using: $mysqli->affected_rows but even though all records have been inserted, I get zero affected rows.

How can I check when commit failed and retrieve the error? Thank you

Answer 1

You could 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 a query goes wrong, it will add the error to the $errors array, so you will know what went wrong. You could also add keys with identifiers for the queries, so you know which query went wrong.

For better handling, you could write a UnitOfWork class for this:

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 could use it like

$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 are doing something like this (pseudo-code) :

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

You will not get the number of inserted rows : the commit instruction is the last executed one, and it doesn't "affect" any row.

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

Returns TRUE on success or FALSE on failure.

If it returned true, then all your previous inserts, since the beginning of the current transaction, will have been commited.

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