Pagination over resultset - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Pagination over resultset (/showthread.php?tid=24715) |
Pagination over resultset - El Forum - 11-17-2009 [eluser]fiktionvt[/eluser] I think my first post was unclear. I am trying to use pagination but instead of paginating an entire table in my database, I want to paginate over a custom query resultset. So for example if my query was Code: SELECT * first_name, last_name, userID FROM users; I would like to paginate over that result set on my view. I have read a lot about the pagination class and have tried all day to get it to work but I am just not understanding. Can someone show me some sample code on how this might be achieved? Thanks Pagination over resultset - El Forum - 11-17-2009 [eluser]Colin Williams[/eluser] [quote author="fiktionvt" date="1258505742"]I think my first post was unclear. I am trying to use pagination but instead of paginating an entire table in my database, I want to paginate over a custom query resultset. So for example if my query was Code: SELECT * first_name, last_name, userID FROM users; I would like to paginate over that result set on my view. I have read a lot about the pagination class and have tried all day to get it to work but I am just not understanding. Can someone show me some sample code on how this might be achieved? Thanks[/quote] Your query needs a limit and offset. Offset will initially be 0, and $perpage should always be the same. These will be the same values you use for your pagination config. Code: SELECT * first_name, last_name, userID FROM users LIMIT $offset, $perpage; Pagination over resultset - El Forum - 01-13-2010 [eluser]CARP[/eluser] Hi Colin I'm having the same problem. I want to paginate a custom query, but $this->db->query method doesn't accept $qtty, $offset parameters. Do you have a practical example so I can modify my model to paginate a big join query? Thanks Pagination over resultset - El Forum - 01-13-2010 [eluser]Colin Williams[/eluser] You just need to limit your query with SQL Code: $this->db->query('SELECT * FROM my_table LIMIT ?, ?', array($offset, $limit)); Pagination over resultset - El Forum - 01-13-2010 [eluser]CARP[/eluser] Thanks Colin, but if I use that sentence with the pagination library, I get this Code: 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 ''20'' at line 7 and this is weird, because the $limit and $offset values are received in the model without these single quotes ''. Some function is adding single quotes to the $limit value Pagination over resultset - El Forum - 01-13-2010 [eluser]Colin Williams[/eluser] That's probably CI escaping the bindings. You might be better off just adding those right in the query, and not binding them with the array. Pagination over resultset - El Forum - 01-15-2010 [eluser]CARP[/eluser] how? (sorry) Pagination over resultset - El Forum - 01-15-2010 [eluser]Colin Williams[/eluser] I'll leave that as an exercise to the reader... |