Codeigniter Pagination: Run the Query Twice?

Question

I'm using codeigniter and the pagination class. This is such a basic question, but I need to make sure I'm not missing something. In order to get the config items necessary to paginate results getting them from a MySQL database it's basically necessary to run the query twice is that right?

In other words, you have to run the query to determine the total number of records before you can paginate. So I'm doing it like:

Do this query to get number of results

$this->db->where('something', $something);
$query = $this->db->get('the_table_name');
$num_rows = $query->num_rows();

Then I'll have to do it again to get the results with the limit and offset. Something like:

$this->db->where('something', $something);
$this->db->limit($limit, $offset);
$query = $this->db->get('the_table_name');
if($query->num_rows()){

    foreach($query->result_array() as $row){

         ## get the results here
    }
}

I just wonder if I'm actually doing this right in that the query always needs to be run twice? The queries I'm using are much more complex than what is shown above.

Answer 1

Unfortunately, in order to paginate you must know how many elements you are breaking up into pages.

You could always cache the result for the total number of elements if it is too computationally expensive.

Answer 2

Yeah, you have to run two queries, but $this->db->count_all('table_name'); is one & line much cleaner.