Random values in MySql query with pagination

Question

I have advanced search on my web page, now how this works is as follows. When a search is made, random results appear on the content page, now this page also included pagination, so my problem is everytime the visitor goes to the 1st page different results appear. Is it possible to use pagination with this, or will the ordring always be random.

I'm using the a query like

SELECT * FROM table ORDER BY RAND() LIMIT 0,20;

Answer 1

You should use a seed for the MySQL RAND to get consistent results. In PHP you do a

$paginationRandSeed = $_GET['paginationRandSeed']?
    ( (int) $_GET['paginationRandSeed'] ):
    rand()
;

and in MySQL you use that seed

"SELECT * FROM table ORDER BY RAND(".$paginationRandSeed.") LIMIT 0,20"

Of course you'll need to propagate the initial seed in the page requests.

Good luck,
Alin