How to report errors in mysqli

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

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-blind 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 - preferably - prepare()/bind()/execute(), mysqli will just silently return false if a query fails, telling you nothing of the reason.

You'll notice the error only when the following command, when 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.

Unfortunately, this error message tells you nothing of the actual problem. It just bluntly says that your query failed, and nothing else.

It happens because by default mysqli is configured to remain silent if a query returned an error. So you can tell that's an extremely inconvenient behavior.

Luckily, mysqli can be configured to throw a PHP exception in case of a mysql error. It means that a PHP error will be thrown automatically every time a query returns an error, without any effort on your part!

Let's see a small demonstration:

// first of all set PHP error reporting in general
// in order to be sure we will see every error occurred 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 a valid SQL statement");
$res->fetch_assoc();

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

Fatal error: Uncaught 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 quite a 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 again: just 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 lead 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:

• in case of a syntax error, the cited query part begins right after the error.
• it means that, therefore, if the error is at the very end of the query, the cited query part would be an empty string. Most likely it happens when you add a variable directly to the query and this variable happen to be empty (yet another reason to use prepared statements as they will make such errors just impossible to appear.