[FIXED] Codeigniter 4 pagination with custome mysql query

Issue

In my Codeigniter 4 project i used $db->query('select * from myTable') method to get data. (used this directly on controller without model)

Is there way to initialize pagination with Codeigniter for this kind of stuff. I'm new to Codeigniter 4 and not worked $this->pagination->initialize($config);

It says Call to undefined method CodeIgniter\Pager\Pager::initialize()

Is there way to overcome this issue without using model, i referred the documentation (CI4) and done lot of google searches and still no luck !

Solution

Basically in query function you can pass what you need. So, just add LIMIT and OFFSET.

$db->query('SELECT * FROM myTable LIMIT 5 OFFSET 0');

So this query should return 5 items, starting from 0. LIMIT means how many items you need, OFFSET where you want to start looking.

You mention, that you're building pagination, so you can set variables, get limit and offset from request. Because params should be different on different pages. Use something like this.

$limit = $this->request->getPost('limit');
$offset = $this->request->getPost('offset');
$this->db->query("SELECT * FROM `kss_filepaths` LIMIT $limit OFFSET $offset");

BUT, try avoid code like this in production, because the LIMIT clause is vulnerable to SQL injection, you can read more about it Here

Answered By - Arvydas97