Welcome Guest, Not a member yet? Register   Sign In
pagination error - help
#1

[eluser]oliur[/eluser]
Can anyone tell me how this $this->uri->segment() is supposed to work in pagination.
I am getting this error message:

Quote: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 '10' at line 1

SELECT * FROM `orders` LIMIT ,10

controller :
Code:
class Site extends Controller{

    public function index(){

        //pagination config
        $config['base_url'] = base_url().'site/index';
        $config['total_rows'] = $this->db->count_all('news');
        $config['uri_segment']= 3;
        $config['per_page'] = 10;
        $this->pagination->initialize($config);
        $page_details['pagination_links'] = $this->pagination->create_links();
        $page_details['post_rows'] = $this->post_model->getPost($this->uri->segment(3),$config['per_page']);
        
        $page_details['title'] = 'title';        
        $this->load->vars($page_details);
        $this->load->view('main');

    }    
}


Model:
Code:
class post_model extends Model{
    function insertAd($data){
        $this->db->insert('rentpost',$data);
    }
    
    function getPost($offset,$perpage){
        
        $sql = "SELECT * FROM `orders` LIMIT $offset,$perpage";
        
        $q = $this->db->query($sql);
            return $q->result();
    }    
}

View:
Code:
<?php
if($post_rows){
    foreach($post_rows as $row){
        echo '<br/>'.$row->title;
    }
}
?&gt;

&lt;?php
echo $pagination_links;
?&gt;
#2

[eluser]Krzemo[/eluser]
Seems like you $offset is empty.
If there is nothing in $this->uri->segment(3) it shuld return FALSE. I'm wondering if there is coercion to int 1 in this case.
Are you using any routings in your app?
#3

[eluser]oliur[/eluser]
not that I am aware of. But when you say routing what do you exactly mean? Sorry, I am still new to CI. Is there something I need to check on the config settings?

I am not sure how do you pass offset in the first page? Does the system automatically reads it from the url ?

Is there anyway someone can let me know if the above code works fine in their system.

Just wondering if it is my configuration that is returning an empty uri segment.
#4

[eluser]Krzemo[/eluser]
show the page url
is it like http://site.com/controller/function/pagenumber ?
#5

[eluser]oliur[/eluser]
First link : http://localhost/kikorben/site/index

2nd link : http://localhost/kikorben/site/index/10

What I can see from the url is that the offset number is missing at the end in the first link and I think there is nothing wrong with that if you do a little modification in your model class

modified code:
Code:
function getPost($perpage,$offset = 0)

This should ensure the first page starts with an offset of 0 by default.

The pagination is working now, as in all of my links are working fine except the first link. I still get that empty offset error message in the SQL for the first link.


Quote: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 '10' at line 1

SELECT * FROM `orders` LIMIT ,10


I've also changed this line of code in my controller:

Code:
$page_details['post_rows'] = $this->post_model->getPost($config['per_page'], $this->uri->segment(3));


EDITED: THIS HAS SOLVED MY PROBLEM

Code:
function getPost($perPage,$offset = 0){
        
        /*
        $sql = "SELECT * FROM `orders` LIMIT $perPage,$offset";
        $q = $this->db->query($sql);
        */
        $this->db->limit($perPage, $offset);
        $q = $this->db->get('orders');
            
        return $q->result();
    }

ANOTHER SOLUTION : I was going wrong in not checking for null values for the offset parameter.

Code:
function getPost($perPage,$offset = 0){

if($offset != '') $offset = $offset;
else $offset = 0;
$sql = "SELECT * FROM `orders` LIMIT $offset,$perPage";
$q = $this->db->query($sql);
return $q->result();
}




Theme © iAndrew 2016 - Forum software by © MyBB