Random order with MySql pagination

Question

Random order with MySql pagination

Navigation

#1 by (2 votes)

1

Hello, I am having to randomly sort result from my query and display paging using mysql. The sort and pagination are working perfectly.

Whenever pagination is done, because of the use of ORDER BY RAND(); , the results are random and repeated in pagination.

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

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

php mysql

asked by anonymous 28.06.2017 / 20:23

1 answer

Query error by date WordPress: Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 2 bytes) [closed]

score 2 · Answer 1

What you can do is:

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

In this way, paging records are selected by% internal% and are then ordered randomly. Without doing this, SELECT is executed before RAND , then each page runs the risk of duplicate records (understand duplicate as already displayed on another page), because as the order will be random, you can not guarantee which will be the records between LIMIT and $inicial .

See working in SQLFiddle .