random record

[vinasite]

hi,

I was wondering if it's possible to select a random record from a table in a mysql database.

i've read the php.net manual, and below is the code that they suggested:

SELECT * FROM table ORDER BY RAND() LIMIT 0,1

but it doesn't seem to work.

can somebody help?

Thank you

[Kevin Yank]

ORDER BY RAND() works only as of MySQL 3.23.3. Paul DuBois' book, MySQL, provides an alternate method that works on older versions of MySQL.

What you need to do is SELECT a column of random numbers and then sort on that column. However, the following query will not quite work:

SELECT ..., RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1

The MySQL optimizing engine sees this query and says "oh, rand_col is just the result of the same function in every row, so I'll just run RAND() once and treat rand_col as if every row contained the constant result of that function." This effectively optimizes the ORDER BY clause right out of the query!

To fool the optimizer into running the RAND() function to get a different random number for each row, refer to another column in the table in the expression. For example, if you have an ID column in your table, you can do this:

SELECT ..., ID*0+RAND() AS rand_col FROM ... ORDER BY rand_col LIMIT 1

Ta da!

[Son Nguyen]

it seems the same thing with length() ???

[Kevin Yank]

Depends how you're using it. Can you provide an example?

[vinasite]

hi,

so what would my sql statement be if i have a table with 2 columns, ID , and Email, and there's 11 records?

forgive me for asking too much since i've just started with this!

[Kevin Yank]

SELECT *, ID*0+RAND() AS rand_col FROM your_table_name ORDER BY rand_col LIMIT 1

[vinasite]

is rand_col a variable?

[Kevin Yank]

Nope, rand_col is a temporary column name (known as an alias). You could actually name this anything you wanted, as long as the two occurrences in the query match.

For more information about aliases, see Part Nine of my article series:

http://www.webmasterbase.com/article...d=228&pid=1066

[vinasite]

Thanks,

I got it to work!

by the way, is there a limit to tables in a database?

[Kevin Yank]

Each table is represented by a file on your hard disk (three files, actually). As long as your file system can accomodate more files, MySQL can accomodate more tables.

You should never need an unbounded number of tables, though.

[vinasite]

thanks a lot!