Quick pagination question

[eluser]worchyld[/eluser]
I finally got pagination to work, but I have a question.

Why does CI need to run two queries to do the pagination job? The first is to count all records, and the other is to get the records from a certain range.

Is accessing the database twice really the right way to do pagination, or have I made a big error?

Code:

class Customer_list extends Controller {

    // Constructor
    function Customer_list() {
        parent::Controller();
        $this->load->database();
        $this->output->enable_profiler(TRUE);
    } // end function

    // A paginated list of all customers.
    function index() {        
        $this->load->library('pagination');
        $this->load->library('table');
            $this->load->helper('url');    
        $this->load->model('Customer_list_model');

        $config['base_url']     = site_url('customer_list/index/');

                // access database once.
        $config['total_rows']   = $this->db->count_all('ci_customers');
        $config['per_page']     = 10;
    
        $num    = $config['per_page'];
        $offset    = $this->uri->segment(3);
        $this->pagination->initialize($config);

                // access the database again?
        $data['results']    = $this->Customer_list_model->get_customers($num, $offset);
        $data['pageTitle']    = 'Some Page Title';

        $this->table->set_heading('ID', 'Name');

        // Load View ------------------------------------------------------
        $this->load->view('customer_list', $data);


    } // end function

} // end class

[eluser]woopsicle[/eluser]
yeah 2 queries is the only way i have seen how to do it too.

[eluser]EugeneS[/eluser]
i can say you that no other way to do pagination at all not only in CI ... in any language which can work with the any SQL server...

1) we need to count total numer of rows to show pages
2) we need to show results FROM .... TO

2 queries is the only correct way to do pagination.

[eluser]optimal[/eluser]
You could probably accomplish this with a single query if you're really motivated to, but it doesn't appear that CI supports this out of the box.

For MySQL, CI calls the "mysql_query" function, which supports only a single statement. The following link has details regarding support for multiple statement execution:

http://dev.mysql.com/doc/refman/5.1/en/c...eries.html

I believe the single statement limitation is viewed as a security feature to prevent SQL injection attacks.

I think Postgres drivers may support (or not disallow) multiple statements by default, and MS SQL Server used to as well (probably still does).

However, you'd be writing your own code and this would likely fall under the category of "premature optimization" unless your application is truly demonstrating performance problems, and in that case the best solution may lie elsewhere (e.g., server or database optimization).

[eluser]Bulk[/eluser]
I have seen hacky ways to acomplish this using one query, but the executition time of a count(*) query is so low it just isn't worth the hassle.

[eluser]champs[/eluser]
And here's a link to my hackish method of pagination, keeping in mind that its performance is completely independent of a query's complexity.

[eluser]worchyld[/eluser]
Hi. Thanks for the reply. I wasn't sure if I was doing Pagination right. I'm sure in some old PHP code I did years back I did it in one fell swoop, or it stored the data in an array, or something.

But it doesn't matter; so long as it doesn't take forever to load, and its quick to run and doesn't cause memory problems then I'm okay.

Thank you for the kind replies.

[eluser]Iksander[/eluser]
CI also supports query caching, I highly doubt running two queries for Pagination will give you a performance hit.