Random Pagination

[spiceweasel]

Hi Guys I need some help this is my first real php website.

Im trying to retrieve results from an accommodation database, to make the results fair I need to return a random set of results so each accommodation get a fair viewing and nobody is always at the bottom.

The problem arises when i paginate the results, because each page executes a separate randomly ordered offset mysql query I can end up showing the some of the same results over and over on multiple pages. This is going to confuse and irritate searchers.

 

How can i achieve results and paginate them where each accommodation gets a fair chance at the first page each time the database is searched?

[Adam]

You can't do it like that, as MySQL will have no way of knowing which have already been used on a previous page... short of storing each used item's identifier within a session and pumping into a WHERE clause within the query anyway -- which is a really bad idea for a number of reasons!

 

You need to have some form of order so that you can tell MySQL where to pick up from in the ordered list on the next request. If the results are based on a search criteria, they should be returned in relevance order. If you want a kind of browse page, then I'd order them by newest first (which means as they're added they would be number #1, and then slowly degrade). If you want more randomness, you could perhaps add several random accommodations on the home page or at the side of the normal listings?

 

Either way though, the kind of ordered yet randomized results you wish aren't possible - or should I say feasible - to the best of my knowledge.

[monkeytooth]

This may be an obtrusive way of applying the logic.. but through php, build a 2D array of random IDs from your table each secondary array within the main array will be a set of I dunno 10 random numbers... Then when the page initially loads you take these random IDs you store in lets say a cookie or a session value so it can be reused for pagination purpose's. Mind you this logic isnt 100% well thought out, but you can still do something like this.. then you find if the cookie/session exists (may want to give it some type of expiration or clearing method). But what you do is in concept each "Pagination" would be the secondary array values. Where you would do a foreach on them and query then show each result based on the IDs within that particular secondary..

 

Example of my idea per say as far as formating

 

array(

  [0] => 5,6,22,30

  [1] => 1,8,10,15

  [2] => 18,31,32,50

)

 

its a half baked logic I know but its an outside the box idea and could work if done right.

[Adam]

That's an interested idea monkeytooth. How many rows are we talking spiceweasel? ... What a bizarre sounding sentence.

[spiceweasel]

Thanks for the ideas guys. I was think something similar to monkeytooth but thought i better check there wasnt a standard way of doing this. I was also toying with the idea of assigning a Pagination ID which i could cycle every 24 hours and then order results by these - again not fully thought out yet but might work. Time to have a play......

[Adam]

It would theoretically work, but I'd consider it a very hacky method.

[maxudaskin]

Get all of the id numbers of the results you want in an array. Use the shuffle() function and call the rows you want using their id numbers.

[sasa]

you can use "... ORDER BY RAND($seed) ..." where $seed is random number generated for pagination

 

see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

[Adam]

you can use "... ORDER BY RAND($seed) ..." where $seed is random number generated for pagination

 

see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

 

Learn something new every day

[maxudaskin]

you can use "... ORDER BY RAND($seed) ..." where $seed is random number generated for pagination

 

see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

 

Learn something new every day

+1