Pagination / Offset Problems

[eluser]ccachor[/eluser]
So I'm doing a basic search form and I could get my search results to display but when I click on the next page all I get is the same page with a different offset in the URL. It's something wrong with the SQL I'm overlooking. Here's my code.

Code:

// Model

    function searchInventory($make, $model, $num, $offset)
    
      {
            
        $this->db->select('*');
        $this->db->from('inventory');
        $this->db->where('make', $this->uri->segment(3));
        $this->db->where('model', $this->uri->segment(4));
        $this->db->limit($num, $offset);
        $this->db->orderby('sellingprice', 'asc');

                
        $query = $this->db->get();
        return $query;
     }

And the Controller

Code:

function search()
    
    {
    
    //Load Pagination
    
            $data['make'] = $this->uri->segment(3);
            $data['model'] = $this->uri->segment(4);
    
            $this->load->library('pagination');
            $config['base_url'] = base_url().'inventory/search/'.$data['make'].'/'.$data['model'].'/';
            $config['total_rows'] = $this->db->count_all('inventory');
            $config['per_page'] = '10';
            $config['num_links'] = 5;
            $config['full_tag_open'] = '<p>';
            $config['full_tag_close'] = '</p>';
            $this->pagination->initialize($config);
            $paginate = $this->pagination->create_links();
            
    //Load Model
            $this->load->model('Inventory_model');
            $data['query'] = $this->Inventory_model->searchInventory($this->uri->segment(3), $this->uri->segment(4), $config['per_page'], $this->uri->segment(5));
            
            
    //Load View
            
            $this->load->view('inventory_view', $data);    
    }

I think it has something to do with setting the $num and $offset variables somewhere. In the guide it says something like $this>db->get('inventory', $num, $offset) but it's not working for me cause I need to use multiple WHERE's. Any advice would greatly be appreciated

[eluser]Kinsbane[/eluser]
Shouldn't the LIMIT clause in the query be the other way around?

I don't know much about ActiveRecord, as it seems like you're using, but, in my experience the offset is the first number of the LIMIT clause, with the number of records returned the second, as in:

LIMIT $offset, $num

edit: IOW, LIMIT 10, 10

(that would be page 2 if you're number of records per page is 10)

[eluser]JamesD[/eluser]
[quote author="ccachor" date="1198132503"]So I'm doing a basic search form and I could get my search results to display but when I click on the next page all I get is the same page with a different offset in the URL. It's something wrong with the SQL I'm overlooking. Here's my code.
...
And the Controller

Code:

function search()
    
    {
    
    //Load Pagination
    
            $data['make'] = $this->uri->segment(3);
            $data['model'] = $this->uri->segment(4);
    
            $this->load->library('pagination');
            $config['base_url'] = base_url().'inventory/search/'.$data['make'].'/'.$data['model'].'/';
            $config['total_rows'] = $this->db->count_all('inventory');
            $config['per_page'] = '10';
            $config['num_links'] = 5;
            $config['full_tag_open'] = '<p>';
            $config['full_tag_close'] = '</p>';
            $this->pagination->initialize($config);
            $paginate = $this->pagination->create_links();
            
    //Load Model
            $this->load->model('Inventory_model');
            $data['query'] = $this->Inventory_model->searchInventory($this->uri->segment(3), $this->uri->segment(4), $config['per_page'], $this->uri->segment(5));
            
            
    //Load View
            
            $this->load->view('inventory_view', $data);    
    }

I think it has something to do with setting the $num and $offset variables somewhere. In the guide it says something like $this>db->get('inventory', $num, $offset) but it's not working for me cause I need to use multiple WHERE's. Any advice would greatly be appreciated [/quote]

Hi ccachor,

Try setting the current page before running '$this->pagination->create_links();':

Code:

$config['cur_page'] = $this->uri->segment(5);

Note: The above is just a crude example, you may need to do some manual validation for empty values and such.

-JamesD