pagination help

#1 / Jan 09, 2010 9:34pm

dadamssg

108 posts

im getting a mysql error with my query for some reason…

heres the function in my model

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

$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?

#2 / Jan 09, 2010 9:54pm

WebsiteDuck
93 posts

You need to order by a column in your table (maybe end?)

ORDER BY end ASC
#3 / Jan 09, 2010 10:09pm

dadamssg
108 posts

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
#4 / Jan 09, 2010 10:16pm
WebsiteDuck
93 posts
Ah well, the first thing I would change is in your controller function, do this:
```
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’]
```
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
#5 / Jan 09, 2010 10:27pm
dadamssg
108 posts
not sure i get what to do with this
```
somefunction($page=null)
```
#6 / Jan 09, 2010 10:44pm
WebsiteDuck
93 posts
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?
```
index($page=null)
{
  $this->load->library('pagination');
  ...etc
}
```
#7 / Jan 09, 2010 10:47pm

dadamssg
108 posts

im not seeing how $page gets assigned to the third uri segment?
#8 / Jan 09, 2010 10:55pm

WebsiteDuck
93 posts

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

#9 / Jan 09, 2010 11:05pm

dadamssg

108 posts

god this is frustrating i don’t know what im doing wrong heres my Frontmodel

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

?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

#10 / Jan 09, 2010 11:08pm

dadamssg
108 posts

and every link pulls up the same first five rows
#11 / Jan 09, 2010 11:18pm
WebsiteDuck
93 posts
I made a mistake in this part in your controller, change it to this:
```
if (isset($page) && is_int($page))
            {
              $offset = ($page-1) * $config['per_page'];
            }
            else
            {
              $offset = 0;
            }
```
Because if the page is 1 then the offset should be 0

Also try taking this line out of your controller:
```
$config['uri_segment'] = '3';
```
Do all 70 rows satisfy this: end >= NOW() ?
#12 / Jan 09, 2010 11:21pm

dadamssg
108 posts

same thing. I cant get the third segment to become assigned to the offset for some reason…
#13 / Jan 09, 2010 11:31pm
WebsiteDuck
93 posts
Sorry, change this line in your controller
```
if (isset($page) && is_int($page))
```
to
```
if (isset($page) && is_numeric($page))
```
I may be causing your frustration 😛
#14 / Jan 09, 2010 11:33pm
dadamssg
108 posts
if i change
```
$data['query'] = $this->Frontmodel->get_events(5,$offset);
```
to
```
$data['query'] = $this->Frontmodel->get_events($config['per_page'],$this->uri->segment(3));
```
it seems to work, but the pagination links don’t correspond and if i just go to the index of my controller i get a sql error because theres nothing in segment 3 of my uri
#15 / Jan 09, 2010 11:34pm

WebsiteDuck
93 posts

Change what I said above, and then post a couple of the pagination links that you’re getting

Thread

dadamssg

WebsiteDuck

dadamssg

WebsiteDuck

dadamssg

WebsiteDuck

dadamssg

WebsiteDuck

dadamssg

dadamssg

WebsiteDuck

dadamssg

WebsiteDuck

dadamssg

WebsiteDuck

Username

Password

Thread

pagination help

ExpressionEngine News!

Username

Password

Email Address

Display Name

Password