There are many examples where you would need to retrieve a random record or more from a set of tables. For example your page template may be cycling through QOTDs, testimonials, offers, specials, and so forth.
To retrieve a random row in MySQL, use the ORDER BY RAND() statement to randomly order the rows of a table. Then use LIMIT x to retrieve the first x rows from the randomised representation of the table.
For example:
SQL:
-
/* Retrieve 5 random special offers to display in your e-commerce store's sidebar */
-
SELECT offer_id
-
FROM special_offers
-
ORDER BY RAND()
-
LIMIT 5
Of course, we can make our query as complex as needed, and then randomly order the resultset.
SQL:
-
SELECT so.offer_id, i.title
-
FROM special_offers so
-
LEFT JOIN items i ON (so.item_id = i.item_id)
-
LEFT JOIN item_categories ic ON (i.item_id = ic.item_id)
-
WHERE ic.category_id = 2344
-
ORDER BY RAND()
-
LIMIT 5
