How to deal with mysqli problems? For example, an error occurs in mysqli_fetch_array(): Parameter #1 must be of type mysqli_result.

Question

In my local/development environment, the MySQLi query runs fine. However, when I upload it to my web hosting environment, I get the following error:

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

This 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 executing the query through phpMyAdmin in the control panel and the result was normal.

Answer 1

in short

1. Always use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) in your mysqli connection code; and always check for PHP errors.
2. Always replace every PHP variable in an SQL query with a question mark and use prepared statements to execute the query. This helps avoid various syntax errors.

explain

Sometimes your mysqli code will generate errors, such as mysqli_fetch_assoc() expecting parameter 1 to be mysqli_result, but actually getting a boolean value..., calling the member function of bind_param()... or similar errors. There aren't even any errors but the query still doesn't work. This means your query failed to execute.

Whenever a query fails, MySQL will have an error message explaining why. In older versions of PHP, these error messages were not passed to PHP and you would just get the obscure error message mentioned above. Therefore, it is very important to configure PHP and mysqli to report MySQL errors to you. Once you get the error message, you can fix the error.

How to get error information in MySQL

First, in all environments, always add the following line of code before the mysqli connection:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

After this, all MySQL errors will be converted into PHP exceptions. Uncaught exceptions will cause PHP fatal errors. So if a MySQL error occurs, you will receive a regular PHP error. This will immediately make you aware of the cause of the error. And the stack trace will lead you to the exact location where the error occurs.

How to get error information from PHP

On the development server, make sure the error message is displayed on the screen, and on the production server, check the error log. See my article on PHP error reporting for details.

Please note that when making AJAX calls, open the Developer Tools (F12) on the development server and switch to the Network tab. Then make a request for which you want to see the results and it will appear in the Network tab. Click on it and switch to the Response tab. You will see the exact output there. On the production server, check the error log.

How to handle the error message you receive

First, you need to find the problem query. The error message contains the file name and line number where the error occurred. For simple code, this is enough, but if your code uses functions or classes, you may need to trace the stack to find the problem query.

After getting the error message, you need to read and understand it. This may sound too obvious, but learners often overlook the fact that an error message is more than just a warning sign, it actually contains a detailed explanation of the problem. You just need to read the error message and fix the problem.

• If the error message shows that a table does not exist, you need to check spelling, typos, and capitalization. Also, you need to make sure that your PHP script is connected to the correct database.
• If the error message shows an SQL syntax error, then you need to check your SQL statement. The problem should be before the part of the query referenced in the error message.

If you don't understand the error message, you can try searching on Google. When browsing the results, choose answers that explain the error rather than directly giving a solution. The solution may not work in your specific situation, but the explanation will help you understand the problem and enable you to solve it yourself.

You also need to believe the misinformation. If the error message says the number of tokens does not match the number of bind variables, that's it. The same goes for missing tables or columns. When choosing, whether it's your own fault or the fault of misinformation, always stick to the former. Again, this may sound arrogant, but the hundreds of questions on this site prove how useful this advice is.

Basic debugging

If your query doesn't seem to be working, there may be four reasons:

1. An error occurred during execution. Already explained above.
2. Due to a program logic error, SQL did not run at all. Add temporary debug output to ensure the code reaches the point where the query is executed.
3. SQL was executed successfully, but the results were viewed in the wrong database. Please double check again.
4. The entered data does not match the database. Here are some suggestions to check it out