[RESOLVED] PHP/MySQL Random Record selection[Help Needed]

[modpluz]

pls how do you select random datas from a MySQL database(really random).

say i have numerous records and i want to select 4(or more) random records each time the page is refreshed or re-visited.

i have read some tutorial/s but it doesn't help my situation and i have also read about the MySQL RAND() function(but i couldn't use it).

thank you.

[visualAd]

Why can you not use the mysql RAND() function? Do you have some kind of moral objection to it?

[visualAd]

You use it in the order by statement:

SELECT * FROM table ORDER BY RAND();

[modpluz]

oh!, from what i read, i thought you can only use RAND() for just one record as in RAND(0, 1).

[visualAd]

Well you can randomize the order of one record if you like. It will be a little it of pointless process :D

[penagate]

Do not use RAND in an ORDER BY clause. This is an O(n) operation which is very slow for large data sets.
Instead, to pick one record at random, just generate a random number using PHP and use that in a LIMIT clause. You can use a SELECT COUNT() query to get the number of records in the table.

$count = $db->query_read_scalar('SELECT COUNT(m.keyid) FROM mytable m');
$random_record = $db->query_read('SELECT m.* FROM mytable m LIMIT '.rand(0, $count).', 1');


This is O(n) too, but much faster.

[penagate]

Here's a better solution.

http://forums.invisionpower.com/index.php?showtopic=196609&view=findpost&p=1306821

[visualAd]

This is only usually an issue when you have a very large dataset. I should have mentioned it because I ran into problems with it before :(

I used something like this which is still within SQL query itself: http://forums.mysql.com/read.php?24,163940,192316#msg-192316

[penagate]

Yes, that's the way used in the "better solution" above.

[visualAd]

The one I found is better because it is on the MySQL forum :p

[loquela]

Why can you not use the mysql RAND() function? Do you have some kind of moral objection to it?

Correct me if I am wrong, but this doesn't actually return a random row. All it does is randomly select a column by which to order your results. This may give the appearance of random row, especially if your table has loads of columns. But what if it only has one column? You'll get the same order every time. :afrog:

[visualAd]

Correct me if I am wrong, but this doesn't actually return a random row. All it does is randomly select a column by which to order your results. This may give the appearance of random row, especially if your table has loads of columns. But what if it only has one column? You'll get the same order every time. :afrog:
I am not quite sure what you are getting at.