Selecting randomly from a database?

[Cremnlin]

Ok, my situation is that I'm trying to select things from a database randomly. There's no random select feature in SQL (as far as I'm aware), so I'm trying to use PHP to randomly select an ID to pick from the database. That's simple enough, but the problem is that I also want to be able to delete things from the database. If the random integer function picks a number of an ID that has been deleted, I'll get an error.I could take all the IDs from the database and then select randomly from the array, but that would be too much information to extract since this is a user-input database and could potentially grow to have an infinite amount of IDs.Does anyone have any ideas or methods that I don't know about?

[justsomeguy]

There's no random select feature in SQL (as far as I'm aware)

Sure there is:SELECT * FROM table ORDER BY RAND() LIMIT 1The RAND function is defined in MySQL, at least.

[End User]

Two methods I've seen used:

SELECT ROUND(RAND() * (SELECT MAX(id) FROM some_table));

and:

LOCK TABLES foo READ; SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo; SELECT * FROM foo LIMIT $rand_row, 1; UNLOCK TABLES;

If you have gaps or missing IDs, you may need to check and loop to make sure you get an existing ID. If you have lots and lots of missing IDs or gaps, this could be a problem..in that case I think all you can do is select a range of IDs and then randomly pick one of them. That is, select all IDs between 12345 and 53645, use PHP to shuffle() them and then pick one off the top of the array.