Verifying MYSQL executed a result not using get_result()

Question

What's the best way to verify mysql executed successfully and then returned a result when you CANNOT use the following code:

$db = dbConnect();
//begin prepared statements to search db
$stmt = $db->prepare("SELECT email,authentication,email_confirm,externalid,password,id, admin FROM users WHERE email=?");
$stmt->bind_param('s',$email);
$stmt->execute();
$result = $stmt->get_result();
if (!$result){
  //error statement
} if (!(mysqli_num_rows($result)==0)){
  //action to perform
} else {
  // no result returned
}

I was using get_result numerous times in my scripts, and my hosting provider doesn't have mysqlnd driver so I have to rewrite a lot of code. I know I am limited to bind_result and fetch(), but I need a little help rewriting the code since my mindset is stuck in the way I first did it.

I'm also using mysqli and not PDO.

Not arbitrary, will throw a fatal error if I use get_result: http://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result/8343970#8343970 — rocket_boomerang_19, Nov 02 '12 at 18:01
you should be checking at the `->execute()` stage to see if something blew up. It would be highly unlikely for a select query to execute properly but then be unfetchable. That'd probably only happy if the sql server suddenly vanished. — Marc B, Nov 02 '12 at 18:18

Jeremy Harris · Accepted Answer · 2012-11-02T18:51:02.403

1

The Mysqli fetch() function will return one of 3 values:

TRUE - Success. Data has been fetched
FALSE - Error occurred
NULL - No more rows/data exists or data truncation occurred

This means you can set your query like this:

$db = dbConnect();
$query = "SELECT email,authentication,email_confirm,externalid,password,id, admin FROM users WHERE email=?";
$stmt = $db->prepare();
$stmt->bind_param('s',$email);
$stmt->execute();
$stmt->bind_result($email,$auth,$email_confirm,$externalid,$password,$id,$admin);

// Will only execute loop if returns true
$record_count = 0;
while($result = $stmt->fetch())
{
    // Increment counter
    $record_count++;

    // Do something with bound result variables
    echo("Email is $email");
}

// After the loop we either ran out of results or had an error
if($result === FALSE)
{
    echo("An error occurred: " . $db->error());
}
elseif($record_count == 0)
{
    echo("No records exist.");
}

edited Nov 02 '12 at 18:51

answered Nov 02 '12 at 18:02

Jeremy Harris

23,007
13
73
124

yep, I started using the while() loop. But if $stmt->fetch() returns false or null, how do you include an error message to say that nothing returned or the execution failed. Can you structure the while loop to have conditional cases on failure or null... – rocket_boomerang_19 Nov 02 '12 at 18:06
If you theoretically had infinite results, the while loop would never end. Hence, after the loop you know you ran out of results or had an error. I'll update the code to show you a way to handle it. – Jeremy Harris Nov 02 '12 at 18:08
ahhh. This makes sense. I was stuck in an if..else mindset and trying to use if somehow. This is perfect! – rocket_boomerang_19 Nov 02 '12 at 18:14
I'm implementing it now, and it appears the variable $result is null after the loop has completed regardless if it was initially set to 1 for true. So my the error message is always displaying. – rocket_boomerang_19 Nov 02 '12 at 18:46
If it is NULL, then you are out of records. If you are trying to check that there wasn't any records at all, then I will tweak the code above to show an example way to handle that. – Jeremy Harris Nov 02 '12 at 18:50
I took out the $result==FALSE portion portion since it wasn't working and is not my biggest concern right now. For some reason, I think $result becomes null at the end of the while loop regardless of its value in the loop...don't know why. This led it to always throwing the db error. – rocket_boomerang_19 Nov 02 '12 at 19:06

Verifying MYSQL executed a result not using get_result()

1 Answers1