Strange behaviour with PHP prepared queries

Question

I use the following code to establish a connection with the database:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mysb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

?>

Then I try to execute this statement from another PHP page and it works only if an update statement gets coded before another select statement:

<?php
  require_once (getcwd()."connection.php");    

  function callme(){
    if (isset($_GET['check'])) {
      $ip = $_SERVER['REMOTE_ADDR'];
      $sql = "UPDATE Table SET IpAddress = ? WHERE Key = ?";
      $stmt = $GLOBALS['conn']->prepare($sql);
      //if (!$stmt)
        //die("Error occurred");
      $stmt->bind_param("ss", $ip,$g);
      $stmt->execute();
    }
  }
    
  if (isset($_GET['g']) && !empty($_GET['g'])){
    $g = $_GET['g'];

    callme(); //called before the select statement below

    $sql = "SELECT T FROM Table WHERE Key = ?";
    $stmt = $GLOBALS['conn']->prepare($sql);
    //if (!$stmt)
      //die("Error occurred");
    $stmt->bind_param("s", $g);
    $stmt->execute();
    $stmt->bind_result($result);
    $stmt->fetch();
   }
?>

This works correctly. p.s: all queries are correct and already tested manually. The strange thing is that if I try to call the callme() function after the select statement like this:

<?php
  require_once (getcwd()."connection.php");    

  function callme(){
    if (isset($_GET['check'])) {
      $ip = $_SERVER['REMOTE_ADDR'];
      $sql = "UPDATE Table SET IpAddress = ? WHERE Key = ?";
      $stmt = $GLOBALS['conn']->prepare($sql);
      //if (!$stmt)
        //die("Error occurred");
      $stmt->bind_param("ss", $ip,$g);
      $stmt->execute();
    }
  }
    
  if (isset($_GET['g']) && !empty($_GET['g'])){
    $g = $_GET['g'];
  
    $sql = "SELECT T FROM Table WHERE Key = ?";
    $stmt = $GLOBALS['conn']->prepare($sql);
    //if (!$stmt)
      //die("Error occurred");
    $stmt->bind_param("s", $g);
    $stmt->execute();
    $stmt->bind_result($result);
    $stmt->fetch();

    callme();//called after the select statement above

   }
?>

I get the following error: Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in /Users/user/Sites/test.php:13 Stack trace: #0 /Users/user/Sites/test.php(32): callme() #1 {main} thrown in /Users/user/Sites/test.php on line 12

where the line 12 is exactly $stmt->bind_param("ss", $ip,$g);. What am I missing here?

Solved:

For those who are trying to resolve this problem just add a:

$stmt->free_result();

after your statements. The question already answered and proposed as duplicated is a little bit off-topic. Check out for PHP Commands Out of Sync error and https://dba.stackexchange.com/questions/130019/commands-out-of-sync-you-cant-run-this-command-now for better understandings.

you dont set or pass `$g` to the function scope, so its erroring, but your not looking for any errors — Lawrence Cherone, Oct 28 '20 at 17:49
@LawrenceCherone no, it's declared before the callme() function in both scenarios so it isn't the problem. In the original problem, that piece of code in the function was placed manually before and after the select statement and the behaviour is identical. — Virgula, Oct 28 '20 at 17:51
*This works correctly.* for the top example, I beg to differ. it also has no `$g` passed to it or defined in callme() function — Lawrence Cherone, Oct 28 '20 at 17:52
@Virgula - You're using `$g` inside your function and there it is undefined. You can read more about [variable scope](https://www.php.net/manual/en/language.variables.scope.php) in the manual. — M. Eriksson, Oct 28 '20 at 17:52
I'll add it in order to let you see that it isn't the problem. — Virgula, Oct 28 '20 at 17:53
pass as a param, or use `global $g;` to bring it into scope, or use `$stmt->bind_param("ss", $ip, $GLOBALS['g']);` though it enters bad practice. — Lawrence Cherone, Oct 28 '20 at 17:54
@MagnusEriksson So I don't know why but I'm not getting the error from that. https://ibb.co/YkDpBfb and the error: https://ibb.co/YycxqDc — Virgula, Oct 28 '20 at 17:59
See a *proper* code [to establish a connection with the database](https://phpdelusions.net/mysqli/mysqli_connect). It will help you to resolve your current problem — Your Common Sense, Oct 28 '20 at 18:01
then, to resolve the error to be revealed, use store_result() or - better still - [get_result()-based approach](https://phpdelusions.net/mysqli_examples/prepared_select) — Your Common Sense, Oct 28 '20 at 18:03
adding `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the connection file I get this new error report: `Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in /Users/..../mytest.php:9` — Virgula, Oct 28 '20 at 18:06

Strange behaviour with PHP prepared queries

0 Answers0