spiceweasel Posted April 13, 2011 Share Posted April 13, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/ Share on other sites More sharing options...
Adam Posted April 13, 2011 Share Posted April 13, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201065 Share on other sites More sharing options...
monkeytooth Posted April 13, 2011 Share Posted April 13, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201073 Share on other sites More sharing options...
Adam Posted April 13, 2011 Share Posted April 13, 2011 That's an interested idea monkeytooth. How many rows are we talking spiceweasel? ... What a bizarre sounding sentence. Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201077 Share on other sites More sharing options...
spiceweasel Posted April 14, 2011 Author Share Posted April 14, 2011 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...... Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201644 Share on other sites More sharing options...
Adam Posted April 14, 2011 Share Posted April 14, 2011 It would theoretically work, but I'd consider it a very hacky method. Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201752 Share on other sites More sharing options...
maxudaskin Posted April 15, 2011 Share Posted April 15, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201839 Share on other sites More sharing options...
sasa Posted April 15, 2011 Share Posted April 15, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201914 Share on other sites More sharing options...
Adam Posted April 15, 2011 Share Posted April 15, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1201927 Share on other sites More sharing options...
maxudaskin Posted April 16, 2011 Share Posted April 16, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233589-random-pagination/#findComment-1202392 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.