Jump to content

Selecting randomly from a database?


Recommended Posts

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...