What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such

Question

In my local/development environment, the MySQLi query is performing OK. However, when I upload it on my web host environment, I get this error:

Fatal error: Call to a member function bind_param() on a non-object in...

Here is the code:

global $mysqli;
$stmt = $mysqli->prepare("SELECT id, description FROM tbl_page_answer_category WHERE cur_own_id = ?");
$stmt->bind_param('i', $cur_id);
$stmt->execute();
$stmt->bind_result($uid, $desc);

To check my query, I tried to execute the query via control panel phpMyAdmin and the result is OK.

Automata

Answer 1

TL;DR

1. Always have mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); in your mysqli connection code and always check the PHP errors.
2. Always replace every PHP variable in the SQL query with a question mark, and execute the query using prepared statement. It will help to avoid syntax errors of all sorts.

Explanation

Sometimes your mysqli code produces an error like mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given..., Call to a member function bind_param()... or similar. Or even without any error, but the query doesn't work all the same. It means that your query failed to execute.

Every time a query fails, MySQL has an error message that explains the reason. In the older PHP versions such errors weren't transferred to PHP, and all you'd get is a cryptic error message mentioned above. Hence it is very important to configure PHP and mysqli to report MySQL errors to you. And once you get the error message, you will be able to fix the error.

How to get the error message in mysqli

First of all, always have this line before mysqli connect in all your environments:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

After that, all MySQL errors will be transferred into PHP exceptions. An uncaught exception, in turn, makes a PHP fatal error. Thus, in case of a MySQL error, you'll get a conventional PHP error. That will instantly make you aware of the error cause. And the stack trace will lead you to the exact spot where the error occurred.

How to get the error message from PHP

On the development server make sure that errors are shown on-screen, while on a live server check the error logs. See my article on PHP error reporting for the details.

Note that in case of AJAX call, on a dev server open DevTools (F12), then Network tab. Then initiate the request which result you want to see, and it will appear in the Network tab. Click on it and then the Response tab. There you will see the exact output. On a live server check the error log.

What to do with the error message you get

First of all you have to locate the problem query. The error message contains the file name and the line number of the exact spot where the error occurred. For the simple code that's enough, but if your code is using functions or classes you may need to follow the stack trace to locate the problem query.

After getting the error message, you have to read and comprehend it. It sounds too obvious if not condescending, but learners often overlook the fact that the error message is not just an alarm signal, but it actually contains a detailed explanation of the problem. And all you need is to read the error message and fix the issue.

• Say, if it says that a particular table doesn't exist, you have to check spelling, typos, and letter case. Also you have to make sure that your PHP script connects to a correct database
• Or, if it says there is an error in the SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error message.

If you don't understand the error message, try to google it. And when browsing the results, stick to answers that explain the error rather than bluntly give the solution. A solution may not work in your particular case, but the explanation will help you to understand the problem and make you able to fix the issue by yourself.

You have to also trust the error message. If it says that number of tokens doesn't match the number of bound variables then it is so. The same goes for the absent tables or columns. Given the choice, whether it's your own mistake or the error message is wrong, always stick to the former. Again it sounds condescending, but hundreds of questions on this very site prove this advise extremely useful.

Basic debugging

In case your query seemingly doesn't work, it can be caused by four following reasons:

1. There was an error during execution. Already explained above.
2. The SQL didn't run at all due to incorrect program logic. Add temporary debugging output to make sure that the code reached the point where the query executes.
3. The SQL was executed successfully but the result is viewed in the wrong database. Double check it
4. The input data doesn't match that the database. Here is some recommendations on how to check it

Cleareyed

@AdamWinter using @ is always wrong, and in this particular case is tenfold. Error messages to your program is the same as a pain to your body. It tells you that something is wrong, like your leg is broken. And you have to fix the leg not just take a painkiller and go on. SAME HERE. PHP tells you there is no variable that you expect. So you have to fix the form or whatever, to make this variable available. Not just write a code to circumvent an error. – Cleareyed

For the most part, I agree, however 'Your Common Sense' i disagree with, "Not just write code to circumvent an error" what do you think a try...catch does, it circumvents the error to prevent the codebase exiting out in an uncontrolled manner, some errors you can't fix in code E.G DB server has gone away, you just have to manage the error and circumvent the error to poduce a valid result. – Ladida

@Ladida that's a proper concern of yours but you are drawing wrong conclusions from it. Of course your application should produce a valid result (which, in case of a "Mysql has gone away" error should be a generic 500 error page). But you have to understand that such a valid result is not a concern of your database code. Your database related code should work with a database. Whereas showing the error page should be a concern of a different code. See here: phpdelusions.net/articles/error_reporting – Cleareyed

i understand that it was just the stuff like ' But never use it just to report errors' it sort of precludes fully objective codebases, E.G my MVC if my models encounter an error they need to handle the error then throw it back up so my controller where model usage is wrapped can deal with the error and presenting the error, in this case, my I'm using it's an API so the error and all trace info is sent out the API call if it came from our Remote system (Origin Verification) as a lot of web stuff dose now PHP backend REACT or Angular Front end – Ladida

@Ladida please read the linked article again. Either way, I don't feel this is the right place for such a discussion. If you have a question regarding error reporting in general, better ask it as a separate question. Cheers. – Cleareyed

"Uncaught exception, in turn, makes a PHP fatal error" Is causing a fatal error a good idea on a PRODUCTION server (for mysql errors)? Aren't there a few situations where the error indicates a problem that your script should deal with, and continue however it can; e.g. inform the user that you can't give them that info right now, while alerting an operator to investigate? EDIT I see, where needed you try..catch mysql errors (that you have converted to php errors). – Option

@Option surely you meant the error handler, not a try catch – Cleareyed

As of PHP 8.1.0, the default setting is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT. Previously, it was MYSQLI_REPORT_OFF. php.net/manual/en/mysqli-driver.report-mode.php – Roadside