PHP Codeigniter Pagination

Question

Hello i am stuck on codeigniter pagination.I am getting total rows 6, per page showing 2 row.

I am getting pagination link {Page: 1,2,3} where 1 is not clickable.

On Clicking Page no:2 my link look like localhost/demo/index.php/home/press/?per_page=0/2 but i am getting error on sql

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 '/2,2' at line 1

SELECT * FROM media where media_type = 'PRESS' AND id <> 0 ORDER BY id DESC LIMIT 0/2

Same Error,pagintaion link getting when i click on link : 3 I dont know how to fix this issue.I am sharing my code which i used on my controller,model and view.

My controller code:

function press() 
{
    $this->load->model('Home_model');
    $page= $this->input->get('per_page') ? $this->input->get('per_page') : 0;
    $this->load->library('Pagination');
    $data['page'] = $this->input->get('page');
    if($data['page'] == '') {
        $data['page'] = $config['per_page'] = '2'; // Per Page
    } else {
        $data['page'] = $config['per_page'] = $this->input->get('page');
    }
    $config['first_url']='0';
    $pageno = $this->input->get('per_page');
    if($pageno == ''){
        $pageno = '0';
    } 
    $url_to_paging = $this->config->item('base_url');
    $config['base_url'] = $url_to_paging.'home/press/?per_page='.$page;
    $return = $this->Home_model->pressdata($config['per_page'],$pageno, $data);

    $data['pressdata'] = $return['result']; // Get Two Row Data
    $config['total_rows'] = $return['count']; // Total Count 6

    $this->pagination->initialize($config);
    $this->load->view('press',$data);
}

Model :

function pressdata($pg_num, $offset, $content) 
{
    if($offset == ''){
        $offset = '0';
    }
    $sql = "SELECT * FROM  media where media_type = 'PRESS' AND id <> 0";
    if($pg_num!=0 || $pg_num!="")
    {
        $sql .= " ORDER BY id DESC LIMIT ".$offset.",".$pg_num;
    } 
    $query = $this->db->query($sql);
    $sql_couint = "SELECT * FROM  media where media_type = 'PRESS' AND id <> 0";
    $query1 = $this->db->query($sql_couint);
    $ret['result'] = $query->result();
    $ret['count']  = $query1->num_rows();
    return $ret;
}

I have wrote tutorial about Cdeigniter Pagination. Please have a look and give your suggestions cloudways.com/blog/pagination-in-codeigniter — Owais Alam

Sayantan Das Sayantan Das · Accepted Answer · 2016-09-09T08:49:23

Try it like this

$this->load->library('pagination');
$this->load->model('Home_model');

$config['base_url'] = base_url().'home/press/;
$config['total_rows'] = 200;   // Total no of rows returned from the database table
$config['uri_segment'] = 3;    // It is the page no,used as offset in model
$offset = 0;

if ($this->uri->segment($config['uri_segment']) != "") {
    $offset = $this->uri->segment($config['uri_segment']);
}

$config['per_page'] = 20;      // you can change it as you want
$return = $this->Home_model->pressdata($config['per_page'],$offset, $data); 
$this->pagination->initialize($config);

In your model you can do something like this

function pressdata($per_page, $offset) {
    if($per_page !== '' && $offset !== '') {
        $this->db->limit($per_page, $offset);
    }
    //rest of your query
}

Don't copy paste it though, I just wanted to give you an idea. Modify it, implement it according to your needs. Let me know if it worked. Go through CodeIgniter's pagination library for more detailed documentation.

PHP Codeigniter Pagination

3 Answers