A function that randomly selects a row from the database!

Question

I am creating an online store website that needs the functionality to select a random product from the database.

The idea is that there will be an advert for a random product that is different each time the webpage loads!

Using PHP, how would I go about doing this?

tbl_products

id code title stock cost rrp

These are the rows I need to get access to from the database.

Thanks

has this not been answered already? http://stackoverflow.com/questions/3624759/fastest-way-get-random-record-from-db http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql — Kevin Burton, Jul 19 '11 at 14:37

Quassnoi · Accepted Answer · 2011-07-19T14:23:55.243

7

A most straightforward solution would be this:

SELECT  *
FROM    tbl_products
ORDER BY
        RAND()
LIMIT 1

However, this becomes less efficient as the number of products grows.

This solution:

Selecting random rows

is more efficient, though it still requires a full table scan.

If you product ids are distributes more or less uniformly, use this:

SELECT  p.*
FROM    (
        SELECT
                (
                (
                SELECT  MAX(id)
                FROM    tbl_products
                ) -
                (
                SELECT  MIN(id)
                FROM    tbl_products
                )
                ) * RAND() AS rnd
        ) q
JOIN    tbl_products p
ON      id >= rnd
ORDER BY
        id
LIMIT 1;

If you have gaps between ids, the products after large gaps will tend to be selected more often.

Instead of id, you may use a special unique column for this purpose which you should fill without gaps in a cron job.

edited Jul 19 '11 at 14:23

answered Jul 19 '11 at 14:17

Quassnoi

413,100
91
616
614

I would add a WHERE stock > 1 and at any one time, the amount of products in stock wouldn't exceed 500. Would your solution work for that? – sark9012 Jul 19 '11 at 14:18
See http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ for some thoughts on why this might not be the most efficient way to do this. – newtron Jul 19 '11 at 14:21
@Luke: with only `500` products the first query would work fast enough. – Quassnoi Jul 19 '11 at 14:27
@cwallenpoole - slight confusion there, I was asking the question would 500 products be efficient enough! newtron, ill take a look at that. quassnoi, thanks bud, ill take a close look at both solutions! Awesome answer :) – sark9012 Jul 19 '11 at 14:38
@quassnoi - for the second solution, how would I incorporate a WHERE stock > 1 in? Is that possible with the max function? – sark9012 Jul 19 '11 at 14:58
@luke: incorporate it into all three queries. However, there is a good chance that this condition will have an impact of the uniformness of the distribution and some of the products will be shown much more often than others. Again, with only `500` products, the `ORDER BY RAND()` should be almost instant. – Quassnoi Jul 19 '11 at 15:26
Yeah i went for the first one in the end. At the moment there are 6 products! But will select 500 stocked products at max I predict when fully functional. Thanks :) – sark9012 Jul 19 '11 at 15:44

score 1 · Answer 2 · answered Jul 19 '11 at 14:26

ORDER BY RAND() is a well-known solution that has well-known problems.

If the product ids are a consecutive range of integers and there is a non-trivial number of rows, then it will much better to SELECT MAX(id) FROM products, generate a number of random integers between 1 and the result in PHP, and do SELECT * FROM products WHERE id IN (x, y, z) as a second query. If the ids are almost, but not quite, consecutive, you can adapt this solution to generate more random ids than needed to account for the fact that not all of them might be valid (the more fragmentation there is among ids, the more surplus numbers you should generate).

If the above is not an option, then querying like this will still be better than a pure ORDER BY RAND().

score 0 · Answer 3 · answered Jul 19 '11 at 14:44

Here's a PHP solution

$range_res = mysql_query( " SELECT MAX(id) AS max_id , MIN(id) AS min_id FROM products ");
$range_row = mysql_fetch_object( $range_res ); 
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$res = mysql_query( " SELECT * FROM products WHERE id >= $random LIMIT 0,1 ");

A function that randomly selects a row from the database!

3 Answers3