0

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?

Flavius
  • 13,566
  • 13
  • 80
  • 126

2 Answers2

3

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
}
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • 1
    Is there any way to make mysqli classes throw exceptions instead of me having to check for errors every time? – Flavius Feb 19 '12 at 16:02
  • @Flavius: I don't thin so (I might be wrong, I haven't worked with php for a while). However, you always have an option to write your own class that inherits from `mysqli` and overrides a couple methods so they throw an exception. – a1ex07 Feb 19 '12 at 16:19
1

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
pcworld
  • 136
  • 2
  • 9