PHP/MySQL: Howto paged with a random order
by Nyro, Tuesday 18 March 2008 at 04:21:38 AM :: Programmation
Let's start with the basis: A normal paging. To do so, you use the LIMIT parameter in the MySQL query:
SELECT * FROM user LIMIT 20,10
Where we show the second page for the users with 10 elements by page.
You will probably order the result to be easier to use. For example:
SELECT * FROM user LIMIT 20,10 ORDER BY name ASC
At this point, everything was pretty easy and you probably already knew that.
Now we want to randomly order the result. Intuitively, you will do something like:
SELECT * FROM user LIMIT 20,10 ORDER BY RAND()
Which is not totally wrong. The problem with this solution will occur when changing the page, the order will be different -for sure, it's random. By changing the page you will probably see some recurrent data, and the visitor will never see all the result by reading all the pages. That's not expected.
The solution consists to generate a random number in PHP, stores it in a session variable, and finally use it in the MySQL query inside the RAND parenthesis. And it's finish. Therefore the random number used is every time the same for the visitor session, and the global order will stay the same in the differents pages.
The PHP code to generate and stored the random number:
$rand = $_SESSION['rand'];
if (empty($rand)) {
srand((float)microtime()*1000000);
$rand = "0.".rand();
$_SESSION['rand'] = $rand;
}
Of course, you have to open the session with session_start() at the top of your PHP script before every out or header send -or you can use ob_start().
Finally the MySQL query becomes:
SELECT * FROM user LIMIT 20,10 ORDER BY RAND($rand)
Voilà, you can make pagination with random order.
Comments.
1. the Thursday 24 July 2008 at 02:54:15 PM, by Gilles
2. the Thursday 21 August 2008 at 03:30:41 PM, by BluesRocker
3. the Friday 27 March 2009 at 04:22:37 AM, by Leonardo
4. the Tuesday 09 June 2009 at 12:58:14 AM, by RLHawk
5. the Tuesday 09 June 2009 at 01:15:04 AM, by RLHawk
6. the Friday 02 March 2012 at 03:24:49 PM, by Mehmet Hazar Artuner
Add a comment.