CODEWITHSUNDEEP
phpmysql
CLiown
CLiown

Reputation: 13853

Selecting random entry from MySQL Database

How can I select a single random entry from a MySQL database using PHP?

I want to select the Author, AuthorText, and Date?

Upvotes: 2

Views: 2043

Answers (4)

Francisco Alvarado
Francisco Alvarado

Reputation: 2813

Take a look to this interesting article:
“Do not use ORDER BY RAND()” or “How to get random rows from table?”

Upvotes: 2

bobflux
bobflux

Reputation: 11591

ORDER BY rand() LIMIT 1

will sort all the rows in the table, which can be extremely slow.

Better solution : say your table has the usual primary key auto-increment field, generate a rendom number between min(id) and max(id) and select the closest id.

It will not be as random as a "true" random selection, because a id after a large hole of deleted ids will have a higher probability of being chosen. But it will take 50 µs instead of 2 seconds if your table is large...

SET @t = (SELECT FLOOR(a + (b-a)*rand()) FROM (SELECT min(id) as a, max(id) as b FROM table)
SELECT * FROM table WHERE id>@t ORDER BY id LIMIT 1;

Upvotes: 1

Jesse
Jesse

Reputation: 479

SELECT Author, AuthorText, Date FROM table ORDER BY RAND() LIMIT 1

Upvotes: 6

Alex K.
Alex K.

Reputation: 175986

You can order by a random & restrict to 1 row as follows:

select 
  author, authortext, date 
from bookstable 
order by rand()
limit 1

Upvotes: 0

Related Questions