Random order with Mysql paging

Asked

Viewed 337 times

1

Hello, I need to sort randomly result of my query and display pagination using mysql. Ordering and paging are working perfectly.

Always when paging is done, on account of the use of ORDER BY RAND(); results are random and repeated in paging.

I would like to know how to use the ORDER BY RAND(); so that the records are always presented randomly in each query (not necessarily in each page) without repeating in the following pages.

SELECT * FROM tabela1 WHERE Id= $id ORDER BY RAND() LIMIT $inicial,$final
php mysql

  • @Everson put it, but the example is indifferent because it is the way to use the randomized result with paging that is the question. Records cannot be repeated on subsequent pages. The random has to be done once in the result of the query without paging.

  • the display is normally done with php, calculating the pagination correctly with the initial number of records, all right.

1 answer

2

What you can do is:

SELECT * FROM (
    SELECT * FROM tabela1 LIMIT $inicial, $final
) as t
ORDER BY RAND();

Thus, the records referring to the pagination are selected by SELECT internal and later are sorted randomly. Without doing this, the RAND is executed before the LIMIT, then each page runs the risk of records being duplicated (understand duplicated as already displayed on another page), because as the order will be random, there is no way to guarantee which records will be between $inicial and $final.

See working on Sqlfiddle.

  • 1

    Well that’s it, perfect! However, let’s assume if you want to do a general randomization and then limit, you will need to store all the data in randomized cache and then perform the JS limitation, because in the second execution of the same SQL the result will not be the same.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.