Random Selection

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pezmc
Forum Commoner
Posts: 53
Joined: Mon Nov 06, 2006 2:15 pm

Random Selection

Post by Pezmc »

In my database I have a hundred or so items layed out like this,

Code: Select all

id   name   shown
1   Pez   yes
2   Giles  no
3   Bob   yes
etc...
I am trying to randomly select one of the entries where shown is yes.
What is an easy way to do this? I have to be able to expand the database and it still work.

So someone clicks give me a name and the php randomly choses a name from the database but only out of ones that shown is == to yes.
However users will be able to add their name to the database, then a admin will alow the name to be shown (to prevent abuse) . And the new name will be added to the list of randomly choosable names.

Thanks,
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

I'm feeling kind...

Code: Select all

SELECT `name` FROM `table` WHERE `shown` = 'yes' ORDER BY RAND() LIMIT 1
To optimise your database I would suggest changing the shown column into either a BOOL or a CHAR(1), if your RDBMS doesn't support boolean data types.
Post Reply