Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can to select a random row from a MySQL database in PHP?

I have a database table questions on a online web server with 2000+ rows and I need to get 6 randomly selected rows. They must be different so that one question is not two times in the list array of 6 questions.

How can I achieve this?

like image 651
Mapi Avatar asked Dec 11 '22 14:12

Mapi


2 Answers

You have a relatively small amount of data, so the simplest method is:

select q.*
from questions q
order by rand()
limit 6;

In this query, the order by takes the longest amount of time. Ordering 2,000 rows might be noticeable. A simple fix is to reduce the number of rows being ordered. One method is:

select q.*
from questions q cross join
     (select count(*) as cnt from questions) m
where rand() < 100 / m.cnt
order by rand()
limit 6;

The where selects about 100 rows randomly and then orders those to select 6. You are pretty much guaranteed that the where will always choose at least 6 rows.

like image 166
Gordon Linoff Avatar answered Dec 13 '22 03:12

Gordon Linoff


Use the DISTINCT operator in MySQL:

SELECT DISTINCT column FROM table ORDER BY RAND() LIMIT 6;

So DISTINCT will take care and remove duplicates

like image 44
S.I. Avatar answered Dec 13 '22 03:12

S.I.