mysqli - handling errors with transactions

Question

How to properly handle errors with transactions and prepared statements when using mysqli?

Snippet:

<?php
$conn = require_once 'dbconn.php';
$conn->autocommit(FALSE);

$stmt_ins_option = $conn->prepare('INSERT INTO options(option_name) VALUES(?)');
$option_name = 'foo';
$stmt_ins_option->bind_param('s', $option_name);
$stmt_ins_option->execute();
$conn->commit();
if($conn->errno) {
    $conn->rollback();
    echo $conn->error;
}

It won't add it a second time because there's a UNIQUE constraint on that column.

However the script won't report any error either.

What am I missing?

Answer 1

execute returns false on failure, so you may want to check it before committing. Also, rolling back in your code has no effect because you committed transaction previously. I'd write something like

try
{
  ....
  if (!$stmt_ins_option->execute())
  {
   throw new Exception("Cannot insert record. Reason :".$stmt_ins_option->error);
   // surely, it's better to define your own exception hierarchy
  }
  $conn->commit();
}
catch (Exception $e)
{
   $conn->rollback();
   // display error/re-raise/ or whatever you think makes sense for your function
}

Answer 2

Is there any way to make mysqli classes throw exceptions instead of me having to check for errors every time

According to this answer you can use the following call to enable exceptions for such errors:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

See mysqli_driver::$report_mode

(Stupid Stack Overflow not letting me make comments)