Microsoft SQL Server 2000 pagination with CI

[eluser]praxedis[/eluser]
1. If you're running Microsoft SQL Server 2000, I feel for you. I only had to deal with this because my day job is dumb enough to be running virtually all its legacy stuff on SQL Server.

2. Stored procedures WILL NOT WORK with active record in CI. If you've written a fancy stored procedure to take care of pagination in SQL Server 2000, congratulations, but it's useless for anyone developing in CI.

3. This tutorial is only for those running SQL Server 2000. SQL Server 2005 has fancy functions like ROW_NUMBER() that make it a little easier to do the pagination.

4. Make sure you add 'pagination' to the list of libraries in your config/autoload.php file or call it up in your controller.

Here we go:

I have a table called 'people_table' that stores person information:

person_id
person_first_name
person_last_name
person_email

In my model, I have these functions.

Code:

function get_all_people() {

        $this->db->select('*');
        $this->db->from('people_table');
        $result_count = $this->db->count_all_results();

        $this->db->select('*');
        $this->db->from('people_table');
        $this->db->order_by('person_last_name', 'asc');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return array (
                'results_count' => $result_count,
            'results' => $query->result());

        } else {
            return null;
        }
    }

function get_paginated_people($limit, $offset) {

    if ($offset == 0) {
            $sql = "SELECT TOP $limit *
            FROM people_table
            ORDER BY person_last_name asc";
        } else {
            $sql = "SELECT TOP $limit *
            FROM people_table
            WHERE person_id NOT IN (
            SELECT TOP $offset person_id
            FROM people_table
            ORDER BY person_last_name asc
            )
            ORDER BY person_last_name asc";
        }

        $query = $this->db->query($sql);

        if ($query->result()) {

            return $query->result();

        } else {
            return null;
        }
}

In my controller, I have:

Code:

function index() {

        //DEBUG
        //$this->output->enable_profiler(TRUE);

        $people_data = $this->peoplemodel->get_all_people();

        //PAGINATION
        $config['base_url'] = site_url('getpeople/index');
        $config['total_rows'] = $people_data['results_count'];
        $config['per_page'] = '20';
        $this->pagination->initialize($config);
        $data['page_links'] = $this->pagination->create_links();

        $limit = $config['per_page'];
        $offset = $this->uri->segment(3, 0) ? $option = $this->uri->segment(3, 0) : 0;
        $paginated_people_data = $this->peoplemodel->get_paginated_people($limit, $offset);

        foreach ($paginated_people_data as $person_data) {
            $data['people'] .= '
            <li>
            ' . strip_quotes($person_data->person_last_name) . ', ' . strip_quotes($person_data->person_first_name) . ' '
            .auto_link($person_data->person_email) . '</li>';
        }

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

And there you have it. Don't forget that auto_link() is a CI URL helper.

Main difference here is obviously the SQL for pagination. There is no other way than using TOP/TOP for SQL Server 2000.

Hope this helps.