count database records + limit

count database records + limit - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: count database records + limit (/showthread.php?tid=21128)

count database records + limit - El Forum - 07-31-2009

[eluser]hsl[/eluser]
Hello,

In my application I have a query that gets a lot of items out of the Database, now I want to limit it to 199 items,
but I want to count all the items that would be in the results if I didn't use the limit.

Is that possible in one query?

I'm using the activerecord method, and can't find a solution in the documentation.

Smile

count database records + limit - El Forum - 07-31-2009

[eluser]darkhouse[/eluser]
Yes, there's SQL_CALC_FOUND_ROWS, so you can use a method in your model like this:

Code:
function get_some_records($limit, $offset, &$total_rows){

   $this->db->select('SQL_CALC_FOUND_ROWS *', FALSE);

   $query = $this->db->get('some_table', $limit, $offset);

   $total_rows = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;

   return $query->result();

}

And then your controller would be like this:

Code:
function index($page=1){

   $limit = 10;

   $offset = $limit * ($page - 1);

   $total_rows = 0;

   $data = array(

      'records' => $this->some_model->get_some_records($limit, $offset, $total_rows),

      'total_rows' => $total_rows

   );

   $this->load->view('some_view', $data);

}

However, I believe there's a performance hit when using SQL_CALC_FOUND_ROWS. It might be wiser to just run 2 queries, one with a count, and the other with a limit.

count database records + limit - El Forum - 08-01-2009

[eluser]naren_nag[/eluser]
If you using CI's active record pattern, then you can use this to get the total number of results for a query you are building.

Code:
$this->db->count_all_results();

Or you can use this to get the total number of rows in a table

Code:
$this->db->count_all();

Please take a look at CI's documentation for the database class.

cheers,

naren

count database records + limit - El Forum - 08-02-2009

[eluser]darkhouse[/eluser]
Good point. I was under the impression that count_all_results() used something like mysql_num_rows() but I actually checked the source, and it just runs a count query. I think that's the smarter way to go.