pagination help

[eluser]dadamssg[/eluser]
im getting a mysql error with my query for some reason...

heres the function in my model

Code:

function get_events($num, $offset)
     {
    $query = $this->db->query("SELECT * FROM test WHERE end >= NOW() ORDER BY ASC LIMIT $num, $offset");    
    return $query;
     }

and heres my controller

Code:

$this->load->library('pagination');
        $config['base_url'] = 'http://mysite.com/mains/index/';
        $config['total_rows'] = $this->db->count_all('test');
        $config['per_page'] = '5';
        $this->pagination->initialize($config);
        
        //load the model and get results
        $this->load->model('Frontmodel');
        $data['query'] = $this->Frontmodel->get_events($config['per_page'],$this->uri->segment(3));

heres the error im getting

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC LIMIT 5, 3' at line 1

SELECT * FROM test WHERE end >= NOW() ORDER BY ASC LIMIT 5, 3

any idea whats goin on here?

[eluser]WebsiteDuck[/eluser]
You need to order by a column in your table (maybe end?)

ORDER BY end ASC

[eluser]dadamssg[/eluser]
alright well thats one fix.

If i just go to mysite.com/mains/index/ and theres no 3rd segment in my uri i get a sql error. but i go to say mysite.com/mains/index/3 i get results BUT the 3rd segment is being assigned to my limit.

so if its 'index/1' ill get one row pulled up, 'index/2' will pull up two rows, etc. And my links on my view page aren't correct at all. No matter what i input for the 3 segment in my uri im always on page 1 and i noticed that page two link is 'mysite.com/mains/index/5' instead of 'mysite.com/mains/index/2'. wtf

[eluser]WebsiteDuck[/eluser]
Ah well, the first thing I would change is in your controller function, do this:

Code:

somefunction($page=null)

That way you can use $page instead of $this->uri->segment(3)

Secondly, you need to multiply your offset by your $config['per_page']

Code:

if (isset($page) && is_int($page))
{
  $offset = $page * $config['per_page'];
}
else
{
  $offset = 0;
}

That should get rid of the mysql error if no page is specified too

[eluser]dadamssg[/eluser]
not sure i get what to do with this

Code:

somefunction($page=null)

[eluser]WebsiteDuck[/eluser]
It's the function in your controller, you didn't include it in your first post so I don't know what your function is called, could be index?

Code:

index($page=null)
{
  $this->load->library('pagination');
  ...etc
}

[eluser]dadamssg[/eluser]
im not seeing how $page gets assigned to the third uri segment?

[eluser]WebsiteDuck[/eluser]
Passing URI segments to your functions

Also I noticed in your model you have $num and $offset switched.
It should be LIMIT $offset, $num

Thats why page 2 returns 2 rows

[eluser]dadamssg[/eluser]
god this is frustrating i don't know what im doing wrong heres my Frontmodel

Code:

class Frontmodel extends Model {



    function Frontmodel()

    {

        parent::Model();

    }

     function get_events($num = NULL, $offset = NULL)
     {
    $query = $this->db->query("SELECT * FROM test WHERE end >= NOW() order by start asc LIMIT $offset, $num");    
    return $query;
     }

and heres my controller

Code:

?php

class Mains extends Controller {

    function Mains()
    {
        parent::Controller();
        $this->load->helper('url');
        $this->load->helper('date');
        $this->load->helper('text');
        $this->load->helper('form');
        $this->load->library('session');
        $this->load->library('form_validation');
    }

    function index($page)
    {
    $newdata = array(
                   'page'  => 'mains',
               );
        $this->session->set_userdata($newdata);
        $data['title'] = "Home";
        $data['heading'] = "All Events";
        //$this->load->model('Frontmodel');
        //$data['query'] = $this->Frontmodel->get_all_events();
        
        $this->load->library('pagination');
        $config['base_url'] = 'http://mysite.com/mains/index/';
        $config['total_rows'] = $this->db->count_all('test');
        $config['per_page'] = '5';
        $config['uri_segment'] = '3';
        $this->pagination->initialize($config);
        
        //load the model and get results
        $this->load->model('Frontmodel');
        if (isset($page) && is_int($page))
            {
              $offset = $page * $config['per_page'];
            }
            else
            {
              $offset = 0;
            }
        $data['query'] = $this->Frontmodel->get_events(5,$offset);
            

        $this->load->model('Loginmodel');
        $data['notifications'] = $this->Loginmodel->get_notifications();
        $this->load->model('Loginmodel');
        $data['messages'] = $this->Loginmodel->get_messages();
        $this->load->view('header_view', $data);
        
        if($this->session->userdata('logname') == "")
        {
        $this->load->view('login_view', $data);
        }
        else
        {
        $this->load->view('logged_view', $data);
        }
        
        $this->load->view('main_view', $data);
    }

the 3rd uri segment isnt corresponding with the page links and it only pull ups the first 5 rows when i have like 70 somethin in the db

[eluser]dadamssg[/eluser]
and every link pulls up the same first five rows