How to report errors in mysqli

  1. TL;DR
  2. Introduction
  3. What to do with the error message you get?
  4. Comments

TL;DR

Add the following line before mysqli_connect() (or new mysqli()):

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);

then get rid of every manual mysqli error reporting code in your scripts.

Introduction

Reporting errors is the most essential thing in programming. A programmer is effectively deaf-mute if they cannot see errors occurred during the script execution. So it is very important to configure error reporting for your code properly, including database interactions.

By default, when you are running mysqli_query() or - preferred - prepare()/bind()/execute(), mysqli will just silently return false if query fails, telling you nothing of the reason.

You'll notice the error only when the following command, trying to use the query result, will raise an error, such as

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given.

or the like, but unfortunately, this error message can tell you nothing of the actual problem. It just bluntly says that your query failed, and nothing else.

It happens because by default mysqli query execution fails silently, reporting no errors whatsoever. So you can tell that's extremely inconvenient behavior.

Luckily, mysqli has the magnificent ability of throwing a PHP Exception in case of mysql error. It means that a PHP error will be thrown automatically in case of any SQL error, without any effort on your part!

Let's see a little demonstration:

// first set general PHP error reporting
// in order to be sure we will see every error in the script
ini_set('display_errors',1);
error_reporting(E_ALL);

/*** THIS! ***/
mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
/*** ^^^^^ ***/
$db $mysqli = new mysqli($host$user$pass$database);

$res $db->query("apparently not valid SQL string");
$res->fetch_assoc();

And run your script again. Whoa! An error appears in a puff of smoke, explaining what was the problem:

Fatal errorUncaught exception 'mysqli_sql_exception'
with message 'You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version
for the right syntax to use near 
'
apparently not valid SQL string' at line 1' in /home/www/test.php:16
Stack trace
:
#0 /home/www/init.php(16): mysqli->query('apparently not ...')
#1 /home/www/test.php(2): include('/home/www/...')
#2 {main}
 
thrown in /home/www/init.php on line 16

As you can see, this is a quite detailed information, including the erroneous part of the query, and even a stack trace that helps you to find a particular function that called the erroneous query.

So, make sure that you always have this magic line before mysqli_connect() in all your scripts:

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);

What to do with the error message you get?

It should be a knee-jerk reflex to copy and paste the error message in Google search. It will leas you to several Stack Overflow questions related to this particular problem with many answers with solutions.

However, there is another possibility. You can try to actually read the error message you get. Usually, the error message explains it pretty straightforward, what is the error. However, some errors could be cryptic. You have to understand some considerations used in Mysql error messages:


Related articles: