phpNoob phpNoob - 23 days ago 13
MySQL Question

Why store result set when determing the number of rows returned in PHP prepared statement for select query?

So after struggling for quiet some time I came across the solution that I need to use store_result() function to transfer the rows returned(from select query) to I don't know where, but it seems unnecessary and even costly to first transfer the results and then checking number of rows.

eg..

$sql = "SELECT * FROM login_info WHERE user_id=? AND password=?";

//Prepare statement
$stmt = $conn->prepare($sql);

//bind parameters to prepared statement
$stmt->bind_param("ss",$userId,$password);

//execute query
$stmt->execute();

//fetch number of rows returned
echo $count = $stmt->num_rows;


$count value is 0. But after adding $stmt->store_result(); to the above code it works.

$sql = "SELECT * FROM login_info WHERE user_id=? AND password=?";

//Prepare statement
$stmt = $conn->prepare($sql);

//bind parameters to prepared statement
$stmt->bind_param("ss",$userId,$password);

//execute query
$stmt->execute();

//store result
$stmt->store_result();

//fetch number of rows returned
echo $count = $stmt->num_rows;


Now code prints the number of rows as 1 in count variable.

Answer

Because prepared queries are unbuffered by default. And num_rows is unavailable for unbuffered queries.

Let me also observe that the number of rows returned by the query is essentially useless. Every time you think you need it, you may find that you already have the information you need. Not to mention that this number if often even being misused, doing some serious harm to the server. So there is little to no use for the num_rows at all.

For example, in your case, obviously you don't need the number of rows returned, but just a flag, whether your query returned anything or not. So you've got plenty of such flags already. the result of fetch() for example or the fetched data itself.