Welcome Guest, Not a member yet? Register   Sign In
Pagination over resultset
#1

[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
#2

[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;
#3

[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
#4

[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));
// Where $limit = per_page setting and $offset is the current offset as passed in the URI
#5

[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
#6

[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.
#7

[eluser]CARP[/eluser]
how? (sorry)
#8

[eluser]Colin Williams[/eluser]
I'll leave that as an exercise to the reader...




Theme © iAndrew 2016 - Forum software by © MyBB