Prepared statements - fetch number of rows then return info

Hi all,

I’ve run into some problems using prepared statements. I have previously used this:

$num = mysqli_num_rows($r);
if ($num > 0) { 
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);

How would this be re-written using the procedural mysqli style?

This is what I have so far:

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
if (mysqli_stmt_num_rows($stmt)>0){

// what to do here?
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

Thanks for any help!

Hi RedBishop, how you doing?

You need to use the mysqli_stmt_get_result function to create a result set from the statement, which you can then pass into the mysql_fetch_array function.

Hey fretburner,

I’m OK, and you?

Yes, I have tried the mysqli_stmt_get_result function and it works, but do you know how I can combine this with
mysqli_stmt_num_rows http://php.net/manual/en/mysqli-stmt.num-rows.php or something similar? I need to find the number of rows returned before I use mysqli_fetch_array.

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

Thanks for your help.

I’m not too bad thanks :smile:

As far as I know, you should be able to add it to what you had already:

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);

if (mysqli_stmt_num_rows($stmt) > 0) {
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
}

Hi, I tried this and receive an error message, mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given. Perhaps one must use another technique to fetch the data after having stored it? Came across this sitepoint post where mysqli_stmt_bind_result is used. Don’t know if I should use that.

Why use mysqli over PDO?

OK, so if $result is a boolean value rather than an instance of mysqli_result, that means mysqli_stmt_get_result is not returning what we expect - if you check the manual page for this function, it says:

Returns a resultset or FALSE on failure.

So there’s a problem with your statement somewhere.

@Michael_Morris,

Hi, as far as I know both fare equally well in preventing SQL injection, but for performance mysqli seems faster. Everyone has a different opinion, I’ll just be glad if I manage to get either of these working :smiley:

@fretburner,

The query works when I don’t use mysqli_stmt_store_result. I see that $result is an object, thus
I tried the following method which works:

if($result -> num_rows > 0){
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
}

Do you think this is OK or is there something faulty with the logic? Thanks!!

Hi, as far as I know both fare equally well in preventing SQL injection, but for performance mysqli seems faster.

It is faster since PDO merely wraps around mysqli, but if the very slight different between the two is critical to you then you’re using the wrong programming language to begin with. I personally don’t consider the lack of code portability to be worth it.

Ok thanks, didn’t know about that wrapping part.

Do you think the code below makes sense? It’s the only way I could access the number of rows. Otherwise I should perhaps try PDO.

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if($result -> num_rows > 0){
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
 }

Thanks for your help.

Well i dont think it makes sense from a situation standpoint - if the number of rows is any-number-greater-than-0, you’d need to loop through them. And at that point, a while($row = mysqli_fetch_array… gets you the same result, without having to look up the number of rows.

If we’re talking about a single-row result set (So you’d be checking for num_rows == 1), then it makes more sense.

Thanks StarLion, I see what you mean, but regardless of the situation do you think the code is OK how it is written - mixing the procedural with the oop? I had a problem using mysqli_stmt_store_result with mysqli_stmt_num_rows, which is why I tried $result → num_rows. Would you say that this is an appropriate way of checking the row count after having used mysqli_stmt_get_result?

Thanks! :smile:

for most cases, get_result is prefered over store_result, so… that’s appropriate. Mixing OOP and procedural… may be a readability issue, but there’s nothing functionally wrong with it.

Great, that’s fine then. Thank you for your help!

Cheers

Hi StarLion,

one of my queries uses variables for the column names. Must these variables be swapped for question mark placeholders? I have tried it this way and the query has failed.

$q = "SELECT n.$name, a.$age, etc..."


$q = "SELECT n.?, a.?, etc..."

Thank you!

since placeholders replace only data—no.

OK, thanks - just checking.

Sorry to bother but I’ve run into another problem, I hope someone can help. Do you know if placeholders must be separated with commas or are spaces allowed?

I’m using two variables in a WHERE clause:

$name = "jo";
$b = "AND a.age IN(33,34,55)";

My usual query is:

WHERE n.name = $name $b AND etc...

This doesn’t seem to work - should it?

WHERE n.$name = ? ? AND etc...

Thanks for any help!

Did you mean to use $name there, to replace the column name?

Hi droopsnoot,

Oops, sorry, that should be:

WHERE n.name = ? ? AND etc...

Thanks