mos wrote:
At 05:08 PM 5/22/2004, you wrote:
Robb Kerr wrote:
Got a simple table that contains records which are made up of only three
fields - ID, quote and author. These are "inspirational" quotes that I want
to appear at the bottom of the pages of my website. I want them to come up
randomly with every page load. How do I randomly access records from a
table?
SELECT ID,quote,author from quotes ORDER BY RAND() LIMIT 1
HTH! --
Unfortunately that will sort the entire table even though it returns only 1 row so it may be too slow for a website if there are a lot of rows in the table.
You will need to create an indexed autoinc field field (if you don't already have one) and use PHP's Math.Random function to pick a single row from the table. The table's autoinc sequence should not have any holes in it. See http://www.phpfreaks.com/quickcode/Random_numbers/87.php for a PHP example on how to use Random.
Mike
You say, "The table's autoinc sequence should not have any holes in it." I fear it isn't clear, however, that that's a requirement to make the code simple, rather than a statement of fact. Unless you never delete a row, or go to great lengths to always reuse deleted IDs, there will be holes in the autoinc sequence, so you will need to code the script to handle the case that the randomly chosen ID doesn't exist. At a minimum, then, your code will do 2 SELECTs to get the quote, one to get the MAX ID, then a loop to get a random ID from 1 to MAX until you get a hit (usually, but not always, on the first try, depending on how many IDs are missing).
If you can live with the quote changing frequently, rather than with every page load, there is an alternative. You could add a column to keep a random order and update it periodically. Something like:
ALTER TABLE quotes ADD rand_order FLOAT; UPDATE quotes SET rand_order=RAND();
Then you can get always get a quote with one SELECT:
SELECT ID,quote,author FROM quotes ORDER BY rand_order LIMIT 1;
Of course, that will keep returning the same quote, so you set up a cron job to periodically (every 5 minutes, for example) run an
UPDATE quotes SET rand_order=RAND();
to change the ordering, effectively choosing a new quote at random.
I suppose if you really wanted a random quote with every page, you could have each page run the UPDATE after running the SELECT, though I suspect that would be no more efficient than the original solution.
Michael
P.S. As a mathematician and programmer, I find the phpfreaks description of random numbers annoyingly imprecise.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
