count database records + limit

[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.

[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.

[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

[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.