I want to request 5 random rows from my SQL table using php. for instance, i need to:
mysql_query("SELECT * FROM catalogue >> not sure what goes here << LIMIT 5");
-
4Don't you notice related topics with exactly the same question asked?Your Common Sense– Your Common Sense2011-11-20 08:03:12 +00:00Commented Nov 20, 2011 at 8:03
Add a comment
|
3 Answers
SELECT * FROM catalogue order by RAND() LIMIT 5
Edit:
For what its worth, Please note that using rand() on a table with large number of rows is going to be slow. This can actually crash your server.
Some Solution:
MediaWiki uses an interesting trick (for Wikipedia's Special:Random feature): the table with the articles has an extra column with a random number (generated when the article is created). To get a random article, generate a random number and get the article with the next larger or smaller (don't recall which) value in the random number column. With an index, this can be very fast. (And MediaWiki is written in PHP and developed for MySQL.)
But this is for a single random row only.
3 Comments
Gucho Ca
Is it good to use something like .. SELECT * FROM catalogue WHERE user_type = 'regular' order by RAND() LIMIT 5 ????
Pheonix
@GuchoCa, Yes that will be fine. Once your table size grows that you start to notice major time spent in this query, you will have to come up with methods to reduce that, like MediaWiki uses.
CodeMonkey
what about you get the length of the table( like check how many records the table has and lets assume 500K which is a great amount of records) then just randomly choose 5 numbers from that total and after that pull those 5 records from the table?
Assuming the table has AUTO INCREMENT on you could get the biggest ID with
SELECT id FROM catalogue ORDER BY id DESC LIMIT 1
and the smallest ID with
SELECT id FROM catalogue ORDER BY id ASC LIMIT 1
making it possible to do this
$randomId = mt_rand($smallestId, $biggestId);
$randomSql = mysql_query("SELECT * FROM catalogue WHERE id='$randomId'");
For five rows you could create the random ID five times.
2 Comments
warren
if that random id is missing, though, the query will fail
Manngo
No, this doesn’t take account of deleted or unsuccessful rows. If you try to add a row, the
id is incremented even if the INSERT fails. That means you’ll have gaps. If you delete a row the id value won’t be reused, so that means more gaps.If you're selecting random rows from a very large table, you may want to experiment with the approaches in the following link:
http://akinas.com/pages/en/blog/mysql_random_row/
note: just to share other options with everyone
1 Comment
Shurvir Mori
This link is not working, it's better that you can share solution, as this is forum for solution, not for link sharing...