Asked  7 Months ago    Answers:  5   Viewed   106 times

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.

 Answers

12

Sometimes your MySQLi code produces an error like mysqli_fetch_assoc() expects parameter..., 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. Unfortunately, by default such errors are not transferred to PHP, and all you've got 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, fixing it will be a piece of cake.

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. 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 a stack trace will lead you to the exact spot where the error occurred.

How to configure PHP in different environments

Here is a gist of my article on PHP error reporting:
Reporting errors on a development and live servers must be different. On a development server it is convenient to have errors shown on-screen, but on a live server error messages must be logged instead, so you could find them in the error log later.

Therefore, you must set corresponding configuration options to the following values:

  • On a development server

    • error_reporting should be set to E_ALL value;
    • log_errors should be set to 1 (it is convenient to have logs on a development PC too)
    • display_errors should be set to 1
  • On a production server

    • error_reporting should be set to E_ALL value;
    • log_errors should be set to 1
    • display_errors should be set to 0

How to actually use it?

Just remove any code that checks for the error manually, all those or die(), if ($result) and such. Simply write your database interaction code right away:

$stmt = $this->con->prepare("INSERT INTO table(name, quantity) VALUES (?,?)");
$stmt->bind_param("si", $name, $quantity);
$stmt->execute();

again, without any conditions around. If an error occurs, it will be treated as any other error in your code. For example, on a development PC it will just appear on-screen, while on a live site it will be logged for a programmer, whereas for the user's convenience you could use an error handler (but that's a different story which is off topic for MySQLi, but you may read about it in the article linked above).

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, 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.

A list of things you should never ever do in regard of error reporting

  • Never use an error suppression operator (@)! It makes a programmer unable read the error message and therefore unable to fix the error
  • Do not use die() or echo or any other function to print the error message on the screen unconditionally. PHP can report errors by itself and do it the right way depends on the environment - so just leave it for PHP.
  • Do not add a condition to test the query result manually (like if($result)). With error exceptions enabled such condition will just be useless.
  • Do not use try..catch operator for echoing the error message. This operator should be used to perform some error handling, like a transaction rollback. But never use it just to report errors - as we learned above, PHP can already do it, the right way.

P.S.
Sometimes there is no error but no results either. Then it means, there is no data in the database to match your criteria. In this case you have to admit this fact, even if you can swear the data and the criteria are all right. They are not. You have to check them again. I've got an article that can help in this matter, How to debug database interactions. Although it is written for PDO, but the principle is the same. Just follow this instruction step by step and either have your problem solved or have an answerable question for Stack Overflow.

Wednesday, March 31, 2021
 
PedroKTFC
answered 7 Months ago
28

For anyone else searching for this solution, as bwewsing suggests I needed to add in store_result however from the links provided above it's not too clear where this should be implemented.

Implementation should take place in the initial calling method. So, as the get_content() method calls the get_template() method it should be implemented here in the following manner:

    private function get_content($pageId){
    $retreiveContent = $this->con->prepare("SELECT template_id, section_title, i1, i2 FROM content WHERE page_id=? ORDER BY sequence DESC");
    $retreiveContent->bind_param('i',$pageId);
    $retreiveContent->execute();
    $retreiveContent->bind_result($template_id, $section_title, $i1, $i2);
    $retreiveContent->store_result();
         while ($retreiveContent->fetch()) {
            //Variables will be populated for this row.
            //Update the tags in the template.
            $template = $this->get_template($template_id);
            $template = str_replace('[i1]',$i1,$template);
            $template = str_replace('[i2]',$i2,$template);
            //$theTemplate is populated with content. Probably want to echo here
            echo $template;
        }
    $retreiveContent->free_result();    
    $retreiveContent->close();
}
Wednesday, March 31, 2021
 
TheLovelySausage
answered 7 Months ago
78

you want the following:

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->execute();
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

this binds both question marks to integer (i) values of $start and $postsPerPage. do NOT use variables directly in prepared statements, because that would defeat the whole purpose of prepared statements (apart from eliminating parsing time)

Saturday, May 29, 2021
 
Gordnfreeman
answered 5 Months ago
28

This answer has been written very long time ago and become irrelevant.

Since then I learned the proper solution for this problem and wrote it in this answer. Please navigate there.

Friday, June 25, 2021
 
msg
answered 4 Months ago
msg
68

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

$connect=&ConnectDB();
// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);
$stmt->execute();

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);
$stmt->execute();

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Remark:

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Thursday, August 5, 2021
 
Shibbir
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :