Counting results from query

How to determine the number of results a PhP query returns

Often the script you are writing requires knowledges of the number of results returned by a query. This could be because of pagination, or because of the design of the page. In any case: You would like to know the number of results returned. Two good ways are available. Which one to use depends slightly on the type of query used, and what the desired count is.

Simple method to determine the number of query results with php.

A straightforward method is availble to determine the number of results the particular query has generated. One does this by using the function “mysql_num_rows()”. Let us start with the basis code used before, to see how it is applied in the code:

Hover over the code, and click on the magnifying glass to copy the source code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
 
// Initialize the variable $count. 
// The value -1 is given to make the difference between no results
$count = -1;
 
$select_query = "
  Select * from my_first_table
    where M_id > 0
    order by M_id ASC
    limit 0 5";
     
  $result = mysql_query($select_query) 
              or die("It was not possible to get the links from the database");
  if($results)
    {
    $count = mysql_num_rows($results);
 
    while($row=mysql_fetch_array($results))
      {
      echo "
     
    <a href="/"$row['M_url']/"">$row['M_name']</a>
       $row['M_description']
     
    ";
    }
  }
?>
<?php

// Initialize the variable $count. 
// The value -1 is given to make the difference between no results
$count = -1;

$select_query = "
  Select * from my_first_table
    where M_id > 0
    order by M_id ASC
    limit 0 5";
     
  $result = mysql_query($select_query) 
              or die("It was not possible to get the links from the database");
  if($results)
    {
    $count = mysql_num_rows($results);

    while($row=mysql_fetch_array($results))
      {
      echo "
     
    <a href="/"$row['M_url']/"">$row['M_name']</a>
       $row['M_description']
     
    ";
    }
  }
?>

As you can see above, this is a really easy way to get the number of results from a query. Only a few additions were made to the script used in the article on selecting from the database. We initiated the variable $count and gave it the value of -1. THis is not particularly needed, but I like to do this so we can always use $count, and test for the negative value in order to find out wether the query gave results. Subsequently we just used to function mysql_num_rows($results) to get the number of resulting rows from the $results reference.

Getting the number of all results when using a limited query

The methode above works well in most situations. HOwever, sometimes you would like to build pagination for your results. For instance: A search functionality on your site may result in 200 results. To make navigation easier, you might want to split these up in 5 pages of 40 results. Naturally, one could of course perform the full query, get the count, and then either loop through the results untill you get the first record to be displayed and then fill the page, or even rerun the query with limits in place to get those results you need. However, as queries get more complex, and your server gets to work on more requests, this method soon starts to require a lot of computing power. So a better way is needed.

A good way to get the count of the total number of records that match a query, even if your query has a limit to the number of records to return is provided by the command SQL_CALC_FOUND_ROWS. In the query we add this directly after the select statement. After performing the query, we can then as mysql to return the number of records found with the command: SELECT FOUND_ROWS(); This is how one implements it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
 
// Initialize the variable $count. 
// The value -1 is given to make the difference between no results
$count = -1;
 
$select_query = "
  Select SQL_CALC_FOUND_ROWS * from my_first_table
    where M_id > 0
    order by M_id ASC
    limit 0 5";
     
  $result = mysql_query($select_query) 
              or die("It was not possible to get the links from the database");
  if($results)
    {
    $count = SELECT FOUND_ROWS();
    while($row=mysql_fetch_array($results))
      {
      echo "
     
    <a href="/"$row['M_url']/"">$row['M_name']</a>
       $row['M_description']
     
    ";
    }
  }
?>
<?php

// Initialize the variable $count. 
// The value -1 is given to make the difference between no results
$count = -1;

$select_query = "
  Select SQL_CALC_FOUND_ROWS * from my_first_table
    where M_id > 0
    order by M_id ASC
    limit 0 5";
     
  $result = mysql_query($select_query) 
              or die("It was not possible to get the links from the database");
  if($results)
    {
    $count = SELECT FOUND_ROWS();
    while($row=mysql_fetch_array($results))
      {
      echo "
     
    <a href="/"$row['M_url']/"">$row['M_name']</a>
       $row['M_description']
     
    ";
    }
  }
?>

Leave a Reply