MySQLi Prepared Report Error Reporting

Question

I am trying to unravel MySqli and am confused by the error message. I use the return value of the MySQLi prepare statement to detect errors when executing SQL, for example:

$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 preparation statement only an error detection in the execution of the SQL statement and no error detection of the execution? If this is the case, I should therefore change my progress bar so that they display errors:

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

And then, to be safe, I also have to do the following after executing the instruction:

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

... Or was I fine and the return value in the MySQLi preparation statement fixes all errors related to the full execution of the query that it defines?

Thanks C

Answer 1

I wrote this twice earlier in the last two days (so for me this is a duplicate, although the questions started a little differently).

Each mysqli method may fail. You must check each return value. If someone fails, consider whether it makes sense to continue an object that is not in the state that you expect from it. (Potentially not in a “safe” state, but I think this is not a problem).

Since only the error message for the last operation is stored for each connection / operator, you can lose information about what caused the error if you continue after something went wrong. You might want to use this information to let the script decide whether to try again (only a temporary problem), change something, or completely release it (and report an error). And it makes debugging 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 it 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 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 in six years ....
The mysqli extension is perfectly capable of performing operations that result in an error (mysqli) other than 0 through exceptions, see mysqli_driver :: $ report_mode .
die () is really very rude, and I would not use it even for examples like this. Therefore, please remove only the fact that each operation (mysql) may fail for a number of reasons; even if the exact same thing went a thousand times earlier ....

Answer 2

Completeness

You need to check both $mysqli and $statement . If they are false, you need to output $mysqli->error or $statement->error respectively.

Efficiency

For simple scripts that may end, I use simple single-line scripts that cause a PHP error with a message. For a more complex application, you must instead activate the error warning system, for example, by throwing an exception.

Use case 1: Simple script

 # This is in a simple command line script $mysqli = new mysqli('localhost', 'buzUser', 'buzPassword'); $q = "UPDATE foo SET bar=1"; ($statement = $mysqli->prepare($q)) or trigger_error($mysqli->error, E_USER_ERROR); $statement->execute() or trigger_error($statement->error, E_USER_ERROR); 

Case Study 2: Application

 # This is part of an application class FuzDatabaseException extends Exception { } class Foo { public $mysqli; public function __construct(mysqli $mysqli) { $this->mysqli = $mysqli; } public function updateBar() { $q = "UPDATE foo SET bar=1"; $statement = $this->mysqli->prepare($q); if (!$statement) { throw new FuzDatabaseException($mysqli->error); } if (!$statement->execute()) { throw new FuzDatabaseException($statement->error); } } } $foo = new Foo(new mysqli('localhost','buzUser','buzPassword')); try { $foo->updateBar(); } catch (FuzDatabaseException $e) $msg = $e->getMessage(); // Now send warning emails, write log } 

Answer 3

Not sure if this will answer your question or not. Sorry if not

To get the error message received from the mysql database about your request, you need to use the connection object as the focus.

So:

 echo $mysqliDatabaseConnection->error 

there will be an echo error sent from mysql from your request.

Hope that helps