MySQLi prepared statements error reporting

Question

I'm trying to get my head around MySQli and I'm confused by the error reporting. I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL, like this:

$stmt_test =  $mysqliDatabaseConnection->stmt_init();
if($stmt_test->prepare("INSERT INTO testtable VALUES (23,44,56)"))
{
 $stmt_test->execute();
 $stmt_test->close();
}
else echo("Statement failed: ". $stmt_test->error . "<br>");

But, is the return value of the prepare statement only detecting if there is an error in the preperation of the SQL statement and not detecting execution errors? If so should I therefore change my execute line to flag errors as well like this:

if($stmt_test->execute()) $errorflag=true;

And then just to be safe should I also do the following after the statement has executed:

if($stmt_test->errno) {$errorflag=true;}

...Or was I OK to start with and the return value on the MySQLi prepare' statement captures all errors associated with the complete execution of the query it defines?

Thanks C

Answer 1

Each method of mysqli can fail. Luckily, nowadays mysqli can report every problem to you, all you need is ask. Simply add this single line to the connection code,

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

And after that every error will reveal itself. No need to test any return values ever, just write your statements right away:

$stmt = $mysqli->prepare("INSERT INTO testtable VALUES (?,?,?)");
$stmt->bind_param('iii', $x, $y, $z);
$stmt->execute();

When the error occurs at any step, it will throw a usual PHP Exception that can be handled or just reported the same way as any other PHP error. Just make sure you configured PHP error reporting properly, i.e. on the dev server errors are displayed on-screen and on the production server errors are never displayed but logged instead.