stramin stramin - 6 months ago 11
SQL Question

MySQL - PHP - Avoiding multiple queries to count results

I am trying to get some results for a query and count all the data results using PHP.

Actually I'm doing something like this:

$MQ=$cnx->query("SELECT COUNT(id) FROM table WHERE field=1;");
$MFA=$cnx->fetch_row();
$count=$MFA[0];
echo "Showing $count results";
$MQ=$cnx->query("SELECT id,name FROM table WHERE field=1;");
while($MFA=$MQ->fetch_assoc()){
// show something with $MFA[id] and $MFA[name]
}


I want to avoid using 2 queries, Is there any way to get the rows count and then get all the results without using an array?

Answer

You can use the num_rows property to get the number of rows in a result set.

$MQ=$cnx->query("SELECT id,name FROM table WHERE field=1;");
$count=$MQ->num_rows;
echo "Showing $count results";
while($MFA=$MQ->fetch_assoc()){
  // show something with $MFA[id] and $MFA[name]
}
Comments