Random entry from random table

[Wojtek]

Hello,

I'd like to select one entry randomly from the array of tables I specify.

How can this be done?

Thanks :)

[atlas]

I'd suggest picking a random table in whatever programming language you're using.

Then, assuming you're using MySQL:



SELECT * FROM $whatever_table ORDER BY RAND() LIMIT 1;


Note that this isn't very efficient at all. If you have unique IDs in your database it might be better to pick a random ID in your program as well.

You can also speed it up by getting just an indexed field back like an id field. I just tried it on a large table of mine, 60,000 rather long records. A "select *" took over 10 seconds to execute, just fetching an ID "select benef_id" took under half a second.

[spec]

create an array of table names


$rand_table = array(1=> 'name', 'etc');
srand()
$rand_table_name = rand(1, [Number of tables]);
$query = "select * from $rand_table_name order by rand() limit 1";


The problem with ordering by the index is if you ever happen to delete an index you will have a hole.