php - MySQLi prepared statements error reporting

itemprop="text">

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 . "            />");

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

itemprop="text">

class="normal">Answer

I wrote
this twice before in the last two days (so for me it's a duplicate even though the
questions started a bit different).

Each method
of mysqli can fail. You should test each return value. If one fails, think about whether
it makes sense to continue with an object that is not in the state you expect it to be.
(Potentially not in a "safe" state, but I think that's not an issue
here.)

Since only the error message for the last
operation is stored per connection/statement you might lose information about
what caused the error if you continue after something went wrong.
You might want to use that information to let the script decide whether to try again
(only a temporary issue), change something or to bail out completely (and report a bug).
And it makes debugging a lot
easier.

$stmt =
            $mysqli->prepare("INSERT INTO testtable VALUES (?,?,?)");
// prepare() can
            fail because of syntax errors, missing privileges, ....

if (
            false===$stmt ) {
 // and since all the following operations need a
            valid/ready statement object
 // it doesn't make sense to go on
 //
            you might want to use a more sophisticated mechanism than die()
 // but's it's
            only an example
 die('prepare() failed: ' .
            htmlspecialchars($mysqli->error));
}

$rc =
            $stmt->bind_param('iii', $x, $y, $z);
// bind_param() can fail because the
            number of parameter doesn't match the placeholders in the
            statement

// or there's a type conflict(?), or ....
if (
            false===$rc ) {
 // again execute() is useless if you can't bind the
            parameters. Bail out somehow.
 die('bind_param() failed: ' .
            htmlspecialchars($stmt->error));
}

$rc =
            $stmt->execute();
// execute() can fail for various reasons. And may it be
            as stupid as someone tripping over the network cable
// 2006 "server gone
            away" is always an option
if ( false===$rc ) {


            die('execute() failed: ' .
            htmlspecialchars($stmt->error));
}

$stmt->close();

edit:
just a few notes six years later....
The mysqli extension is perfectly
capable of reporting operations that result in an (mysqli) error code other than 0 via
exceptions, see rel="noreferrer">mysqli_driver::$report_mode.
href="http://docs.php.net/die" rel="noreferrer">die() is really, really
crude and I wouldn't use it even for examples like this one anymore.
So
please, only take away the fact that each and every (mysql)
operation can fail for a number of reasons; even
if the exact same thing went well a thousand times
before....