How to random select a row from a mysql query result?

[irock]

Hi, I have successfully selected 4 rows using a mysql SELECT query. However, I would like to random pick a row from this result so that every page refresh will yield something different.

Is there anyway to do this?

Thanks much!

[killroy]

Of course there is ;)

SELECT * FROM tablename ORDER BY RND() LIMIT 1;

randomly sort them and then pick one.

SN

[irock]

Thanks killroy... however, I have already executed the mile-long SELECT query. So is there anyway to random select from an already-selected result.

Thanks for the help!

[irock]

Killroy, there's actually one more problem; I need to random pick one row from the latest 5 products. (I'm dealing with a huge product database here.) So, if I use this query, I could get an old product as result.

SELECT * FROM tablename ORDER BY RND() LIMIT 1;

Do you have other solutions?

Thanks very much!

[killroy]

well, you can concatenate order bits...
Let me explain... (uh, and BTW it's RAND not RND)

let say these are your order IDs (or dates or whatever):

001,002,003,004,005,006,007,008

And you want to randomly pick an item with an id>=005 simply:

SELECT * FROM tablename where id>=005 ORDER BY RAND() LIMIT 1;

if you want you can show one of the queries you want to select a random element from. I'm sure there is a simple SQL way to do it (there is for almost anything.)

SN

[irock]

Thanks, but the problem is that I don't know the ID that should be in place of the '005' in your example. The reason is that my current query merely retrieves the latest 4 product rows. So, I cannot really 'define' the value like in your sample code. Adding more complexity to the program is that the database has 30 different categories.

I suppose there isn't a way to random pick one of the 4 product rows with the largest IDs, right?

[killroy]

Could you jsut post the query please? that would make it a lot simpler.

SN

[irock]

Sorry... Shouldn't have complicate this matter.

Here's the query.

$newprod_result = mysql_query("select interface, onelinesummary, picture, productname, DATE_FORMAT(release, '%M %Y') AS verbaldate, categories.subcat, categories.maincat from hardware, categories WHERE interface!= 'Bluetooth' AND status = 1 AND subcategory = $subcat AND categories.id = $subcat order by hardware.id desc limit 4");

[killroy]

hmm, if the number of results retrived is so small, you could fitler one out in the end using HAVING...

here is somethign that works sort of:

SELECT
interface
,onelinesummary
,picture
,productname
,DATE_FORMAT(release,'%M %Y')AS verbaldate
,categories.subcat
,categories.maincat
FROM hardware,categories
WHERE interface!='Bluetooth'
AND status=1
AND subcategory=$subcat
AND categories.id=$subcat
ORDER BY ROUND((10000000000-hardware.id)/4)*4+ROUND(RAND(4)*3)hardware.id
LIMIT 1;

This will retrieve one random line from the the last four records ONLY if all ID'S are exactly one appart.

Alternatively you could select into a temporary table and hten retrieve one row. But since the data is little, I'd jstu select one on the client end in php.

SN

[irock]

Thanks again for your help.

"This will retrieve one random line from the the last four records ONLY if all ID'S are exactly one appart."

Unfortunately, the IDs aren't exactly consecutive.

How do I go about the alternate solution?

Actually, I was thinking of something more 'conventional'. Rather than trying to complicate matters inside one single query, I can do the same select query and then random pick a row first.

Do you know how to do that? (Pick a random row from a fetch query, I mean)

[slade7]

I haven't used this before, but I have an site that's going to need it soon & have done a little looking into it.

array_rand ( array input [, int num_req])

[php.net ]

or shuffle -
[php.net ]

[jatar_k]

array_rand would work if the set is small, another option woul be since there are 5 in the array you just do.

$randrow = rand(1,5);
echo $yourarray[$randrow];

you could also use it more dynamically in the case that you are not always getting 5 rows.

$max = count($yourarray);
$randrow = rand(1,$max);
echo $yourarray[$randrow];