Mysqli Prepared Statements Problem

[mds1256]

Hi all

 

I cannot seem to get the following code to work correctly.

 

Basically I have created a new mysqli() and stored this in a variable called $myCon.

 

I then pass this variable by reference so I can use the same connection into the functions.

 

The first query runs fine but queries after the first one dont work and I get the following error message:

 

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

 

$myCon = new mysqli('localhost', 'surname', 'pass', 'dbHere');

signIn(&$myCon); ----- referenced as the $myCon var is stored in a different PHP file and included




function signIn($Con)
{
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "call sp_checkLoginDetails(?, ?)";
$stmt = $Con->prepare($sql);
$stmt->bind_param('ss', $username, $password);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows != 1)
{
$error = "<div id=\"formMessages\">Username or Password is incorrect. Please try again.</div>";

if($username == "username...")
{
$username = "";
}
}
else
{
$stmt->bind_result($userID, $firstNme);
while($row = $stmt->fetch())
{
$_SESSION['usrID'] = $userID;
$_SESSION['firstName'] = $firstNme;
}
$_SESSION['isLoggedIn'] = true;
$stmt->close();
$sql = "call sp_updateLoginDateTime(?)";
$stmt = $Con->prepare($sql);
$stmt->bind_param('s', $_SESSION['usrID']); ----- this is where it gets the error
$stmt->execute();
header("Location: .");
die();}}

[mds1256]

If I create a new mysqli object just below the last stmt->close then it works so I am guessing that the mysqli object is being closed / destroyed and will not allow further queries to be ran.

 

Why is this happening and is there a way around it.

[PFMaBiSmAd]

Do you have php's error_reporting set to E_ALL, so that more than just fatal runtime errors will be reported, and you need to test if your database statements are failing or not before using the result from one statement in the next to prevent follow-on errors, and to report/display/log your own application errors when a statement does fail so that you can find and fix the problem. Your ->prepare() statement is failing, but you have no error checking/error reporting/logging logic in your code to get your code to tell you why.

 

The ->prepare() method will return a false value if it fails. You need to test for that and if it is false, the $Con->error property will contain the error that occurred for the ->prepare() statement.

[mds1256]

I believe the error_reporting is set to E_ALL in the php.ini

 

ok here is the code with a little error checking.

 

The error I get is: Commands out of sync; you can't run this command now

 

               $username = $_POST['username'];
 $password = $_POST['password'];

 $sql = "call sp_checkLoginDetails(?, ?)";
 $stmt = $Con->prepare($sql);
 if($stmt)
 {
  $stmt->bind_param('ss', $username, $password);
  $stmt->execute();
  $stmt->store_result();
  if($stmt->num_rows != 1)
  {
   $error = "<div id=\"formMessages\">Username or Password is incorrect. Please try again.</div>";

   if($username == "username...")
   {
 $username = "";
   }
   $stmt->close();
  }
  else
  {
   $stmt->bind_result($userID, $firstNme);
   while($row = $stmt->fetch())
   {
 $_SESSION['usrID'] = $userID;
 $_SESSION['firstName'] = $firstNme;
   }
   $_SESSION['isLoggedIn'] = true;
   $stmt->close();
   $sql = "call sp_updateLoginDateTime(?)";
   $stmt = $Con->prepare($sql);
   if($stmt)
   {
 $stmt->bind_param('s', $_SESSION['usrID']);
 $stmt->execute();
 $stmt->close();
 header("Location: .");
 die();
   }
   else
   {
 echo $Con->error;
   }


  }

 }
 else
 {
  echo $Con->error;
 }

[mds1256]

Having read a little more about this problem it seems that you need to return all results before a 2nd query can run as it blocks the connection (hence why If I create a new db connection it will work).

 

see: https://bugs.php.net/bug.php?id=54444

 

So I tried the $stmt->get_result();

 

BUT my web host doesnt have the mysqld (native driver) and will not support this.

 

Is there any work around for this, plenty people are using prepared statements, someone must have came across this issue?

[mds1256]

Further developments....

 

It seems that this works when not calling mysql stored procedures but when you are you have to call: $Con->next_result();

 

This clears the result set even if there are no further results.

 

http://www.php.net/manual/en/mysqli.query.php#102904

[DavidAM]

Yes, a mySql stored procedure returns two "results sets". So you have to call next_result to "clear the pipe", so to speak, and allow other data to come through. If you look at mysqli.multi-query the examples show one way to do it (generically). Basically, you can call $con->more_results() to see if there is more; and call $con->next_result() to get the "more results".

 

I'm not sure how this is impacted by prepared statements, I haven't tried it that way.