CodeIgniter Forums
Pagination on Random Result Set ? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Pagination on Random Result Set ? (/thread-1977.html)



Pagination on Random Result Set ? - El Forum - 07-09-2007

[eluser]chrisco23[/eluser]
Someone out there must be using the Pagination class on a randomly-ordered query result set?

Can anyone recommend a good way to go about this? An example maybe?

Do you do one query and keep all the results in some session variable?

Two things I need to make happen:

1. You click on a result, you click the back button, results are in same order.
2. You get a paginated list of results in a random order, but the order stays the same as you traverse the pagination.

I would think one method solves both problems just not sure what is best and know this must be a solved problem already.



Thanks,
Chris


Pagination on Random Result Set ? - El Forum - 07-09-2007

[eluser]sophistry[/eluser]
maybe try not to store the entire result set in a session - it can get slow if you put too much data in a session variable because (generally speaking) sessions write/read the hard disk and are loaded on every page view where sessions are invoked.

i haven't done this but it is an interesting problem...
here's something to try:
PHP's array multisort

store an array of unique ids (primary keys from your main random recordset) in a session var.

then, just use the ids for subsequent db queries and sorting (using array_multisort() ).


Pagination on Random Result Set ? - El Forum - 07-10-2007

[eluser]chrisco23[/eluser]
Sophistry,

Thanks for the tip. I'm going to look more closely at the pagination stuff today. I implemented it initially about a year in development and since we launched in November, the result sets have been small enough that it hasn't become a bothersome problem until now.

I was thinking I'd store the result IDs in a session variable as you say. Right now that would be integers, approximately #1 - 300. If the site should really take off, I could not see the number ever growing beyond 5000 or so, but still that seems like a lot to carry around in a $_SESSION.(?)

Can you explain where array_multisort() comes into the picture though? The result of the query will be a random order, but I just need to keep that same order throughout the pagination. I'm not sure how the multisort facilitates that.(?)



Thanks,
Chris

P.S. Did everyone's posts get wiped out and reset when the new version of codeigniter got launched? I guess I found codeigniter to be so solid and intuitive that I haven't had any questions since before then Smile


Pagination on Random Result Set ? - El Forum - 07-10-2007

[eluser]sophistry[/eluser]
I've been thinking about this more... array_multisort is not going to help - I was thinking you had a different issue.

You have to have a well-thought-out and sensible design when you've got a query returning random records (a subset of the whole set I assume) and a limit clause in effect for pagination. Basically, if you want to keep the same result set you can't use LIMIT and random results at the same time. That is because if you LIMIT a random recordset and you do a new query for the next page, you'll never have the same set again. So, you can't LIMIT and get random recordsets at the same time - it just won't work.

You have to return the whole recordset once, store it and then do your limiting and paging off the temporary stored recordset. So, make a SESSION var hold the whole set or build a db table (better) to hold the recordset (which you can then LIMIT in subsequent requests). If you build a db table you can also set ordering values in a field and then do ORDER BY.

Looking around the net I found this little MYSQL fragment (you can order by field and set the custom sort order):
select * from pet order by field(species, 'cat', 'dog', 'bird');

I'll keep that trick in my back pocket should I need it...

There was some shakeout when the CI website was re-launched. all usernames got ci_ prepended or appended (can't remember which). So, you had to login with ci attached to your username the first time.


Pagination on Random Result Set ? - El Forum - 07-11-2007

[eluser]chrisco23[/eluser]
I started down the path of session variables and temporary tables when suddenly I thought of a very simple solution that works fine for my needs.

Our site just needs to give equal exposure to results so we need them to be randomized generally but not every single query.

So I just generated a random-number seed and put that in a session variable. Then I do my query with rand(seed) instead of just rand(). It turns out in my case that it's actually desirable to have the same random order throughout a session.


Chris


Pagination on Random Result Set ? - El Forum - 07-11-2007

[eluser]sophistry[/eluser]
awesome! that's the kind of solution I love to see. a seeded random number... I've always thought "now why would I use that?"... now I know.