Problem retrieving specific MySQL record

...while using a random number

[Storyman]

So far I've been able to accomplish simple task with PHP/MySQL (like LeftJoin)--so my MySQL experience is somewhat limited.

The table (quotes) has four fields:
quote_id, category, author, quote

I am able to get the number of records($total), then create a random number ($num) between 1 and $total.

What I'd like to do is to select the record where quote_id = $num.

Since it is a fairly large database (several hundred quotes) I'd prefer not to follow the tutorials that create a array of the entire db, then selects from the array using the random number.

Is there a way to pull from the table the specific record that corresponds to the random number?

[Robber]

How about a WHERE clause, eg:

SELECT blah blah ... WHERE quote_id = $rand

Or have i missed something?

[killroy]

You could also do this:

SELECT *
FROM quotes
ORDER BY RAND()
LIMIT 1;

This will return 1 random quote from which you can then read the quote_id.

SN

[Adrian2k4]

try benchmarking the two - probably selecting one random quote is faster than sorting ALL quotes randomly and then selecting the first one.

[Storyman]

Everyone,

Thanks for the input, which made it possible for me to find the solution I sought.

SELECT quote_id, author, quote
FROM quotes
WHERE quotes.quote_id = random

(Dreamweaver uses variables where "random" is assigned a variable: random = $num)

The key was to first create a recordset that contained only quote_id. That allowed the quote_id total count to be retrieved. Once it was retrieve the variable number was created, then assigned to $num.

The feedback is greatly appreciated.