CodeIgniter pagination with Active Record

0
votes

I think everyone's (mostly beginner of CI users) facing difficulties in pagination using with active record.

In CI's official documents, there is no clear statements explains how to use pagination class object in active record. My problem is; can not define current page and set it to SQL statement. Therefore, when program fetches data from MySQL with pagination, all page navigates first 0 and $config['per_page'];. I mean if I set to per_page=20, all pages link's fetches 0,20 of MySQL rows.

My SQL Statement is:

$this->db->query("SELECT b.*, a.adCampaignTitle FROM ads a, sms b WHERE b.`smsAd_ID`=a.ad_ID LIMIT " . $config['per_page'])->result();

My Pagination Class properties :

$this->load->database();
$config['base_url'] = 'http://localhost/index.php/admin/index/page/';
$config['total_rows'] = $this->db->count_all('sms');
$config['per_page'] = 2;
$this->pagination->initialize($config);

If somebody help me to how-to-initialize and set to current page into SQL part I will be very glad ...

2
phpmysqlcodeigniteractiverecordpagination

2 Answers

1
votes

Looking at your URL, the 4th segment will contain the offset.

Therefore, you can use:

$offset = $this->uri->segment(4, 0);

$this->db->query("SELECT ... LIMIT {$offset}, {$config['per_page']}");
1
votes

We can do this things couple of ways..!

  1. You must have page number with you. Like each and every call you just move to a page number.

That parameter can be called as page_number

so your url will be look like as a http://server.com/index.php/controller/function?page_number=2

So you just need to get the ($this->input->get('page_number') * $config['per_page']) will become as a offset and then limit will be $config['per_page'].

  1. Using the same calculation, you can do in view file and pass it to url or post parameter like limit=1&offset=2..!

i will not say that this is best but its a solution.

You choose what you want..!