MySQLi prepared statements error reporting [duplicate]

by

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();

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 mysqli_driver::$report_mode.
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….

More Related Contents:

  1. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  2. How to use mysqli prepared statements?
  3. Example of how to use bind_result vs get_result
  4. mysqli: can it prepare multiple queries in one statement?
  5. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  6. using nulls in a mysqli prepared statement
  7. Is mysql_real_escape_string() necessary when using prepared statements?
  8. How to prepare statement for update query? [duplicate]
  9. Using Prepared Statement, how I return the id of the inserted row?
  10. How to prevent duplicate usernames when people register?
  11. SELECT COUNT(*) AS count – How to use this count
  12. Why is using a mysql prepared statement more secure than using the common escape functions?
  13. Call to a member function execute() on boolean in [duplicate]
  14. Mysql No connection could be made because the target machine actively refused it
  15. How can I Use Prepared Statements in CodeIgniter
  16. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  17. How to connect to MySQL database in PHP using mysqli extension?
  18. mySQLi prepared statement unable to get_result()
  19. Calling stored procedure with Out parameter using PDO
  20. When *not* to use prepared statements?
  21. WordPress prepared statement with IN() condition
  22. Using PHP to execute multiple MYSQL Queries
  23. Using wildcards in prepared statement
  24. Is using prepared statements necessary? [duplicate]
  25. How to run multiple insert query in SQL using PHP in one go?
  26. Which is fastest in PHP- MySQL or MySQLi?
  27. Create mysql trigger via PHP?
  28. How to make mysqli connect function?
  29. Dynamically bind mysqli_stmt parameters and then bind result
  30. Check to see if an email is already in the database using prepared statements

Leave a Comment