Quick Tip: PHP MySql Pagination with Random Ordering

I recently got to work a site which was more like a directory. One of main trick there was to show random advertiser on the site so that each advertiser get’s some chance for displaying on sites front page. That was actually easy until this has to go with pagination. Back then I didn’t even knew if there’s any way to achieve mysql pagination with random ordering.

Problem is, you show some random advertisers on 1st page but on second page you must see another set of random advertisers (excluding what you already seen on 1st page) and so on. you see where’s it’s going?

Mysql Rand(seed) function to rescue

SELECT * FROM your_table ORDER BY RAND(351) LIMIT 10, 10;

If a constant integer argument N is specified, it is used as the seed value. In the example above the result order is rand, but it is always the same. You simply change the seed and you get a new order.”

Here’s the summary of what we did:

  1. When user land’s on first page we generate a random number in php
  2. This random number serves as seed value to rand function of mysql in our query for page 1. and then this seed is saved to session.
  3. When user visits subsequent page i.e page 2,3 we use the seed value from session.
  4. when user lands on first page again, reset the seed and save new seed to session.

Trick here is, mysql’s rand() function will produce exactly same ordering until you change the seed value.

you should still stay aware that this might turn it to be a bit slower on larger datasets. In that case I would rather go with an extra column in my table and store a random number there and keep changing that periodically.

UPDATE table SET rand_col = RAND(); 

Share if you enjoyed reading it 🙂

The following two tabs change content below.

Ankit Kumar

Team Leader at Logiciel Solutions
This is my personal blog. I post about Laravel, Angular, SQL and Web Technologies here. I have been into web development for 7 years and learning new things always interest me. Looking forward to find a teacher in you all.