A function that randomly selects a row from the database!

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 dat开发者_开发知识库abase.

Thanks

---

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.

---

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().

---

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 ");