Count number of rows returned in Mysql with LIMIT Clause

Knowledge of total number of rows returned from a MySql Query is very important for pagination purpose.

But it becomes difficult to count total number of rows with "LIMIT" clause. Solution to this problem is to use "SQL_CALC_FOUND_ROWS" function which helps in getting results quickly.

Today i will show you, how to use "SQL_CALC_FOUND_ROWS" function. below is a sample code, you can see the query structure

    $query = mysql_query("select SQL_CALC_FOUND_ROWS id from book  LIMIT 0,10");
    $rows = mysql_query("SELECT FOUND_ROWS() AS `found_rows`");

Now check the result as follows

    while($res1 = mysql_fetch_assoc($query))
    {
     echo $res1['id'].'<br/>'; 
    }
    
    print_r(mysql_fetch_assoc($rows));
    

The result,

51
52
53
54
55
56
57
58
59
60
Array
(
    [found_rows] => 67
)

SQL_CALC_FOUND_ROWS and Problem with Codeigniter

"SQL_CALC_FOUND_ROWS" function does not work in codeigniter. So total number of rows returned can be counted as follows.
Here i am using codeigniter's "Active Record Caching" to avoid writing same code twice.

$this->db->start_cache();
    $this->db->select('b.*, u.username');
    $this->db->from('book b');
    $this->db->join('users u', 'b.userid = u.id');
    $this->db->where('b.cat', 'science');
$this->db->stop_cache();

$totalRows = $this->db->count_all_results();


$this->db->limit(20);

$query = $this->db->get();
$data->books = $query->result();

$this->db->flush_cache();

echo $totalRows;

Did you find this post useful? Let Us know in the comments section below.

Posted in: mysql, php, script