Random Query

[mdell]

I have seen a few examples of creating random passwords and ids, but does anyone have an idea about a getting a random item from a MySQL database? Like: function rand($query=$link)
$echo=rand
Thanks
Matt

[tasistro]

How about... given a certain table

$tablecount=%amount of rows in table% (get it through a SQL)

$yourkey=rand(1,$tablecount);

// run a SQL on the dbase something like

$sql_cmd="SELECT %whatever% FROM %your table% WHERE 1 LIMIT ".$yourkey.", 1";

$sql_result=mysql_query($sql_cmd,$db....blablalba...

Will return the record in position $yourkey.

Saludos
Gerardo

[spxdcz]

Or the best way, in mysql, is:

select * from tablename order by rand() limit 1

[[deleted]]

"WHERE 1" ? :-)

[spxdcz]

What do you mean by "Where 1"? Am I missing something?

[[deleted]]

"WHERE 1" is always true, so it serves no purpose. You can leave it out. 🙂

[spxdcz]

You're absolutely correct - that's why I said 'limit 1', not 'where 1'. 🙂

[[deleted]]

yes YOU did, but my reply was to Gerardo, not you. :-P

[spxdcz]

I see... now everything has become clear...

[tasistro]

Dan:

Can you explain this further.

I understand you ORDER BY a column name or column alias, but rand()?

Saludos
Gerardo

[[deleted]]

rand() is a MySQL-only thing that generates a new random value every time the query evaluates it. In this example it is evaluated once for every record, and thus it will give a random number for each record. If you then order by those numbers, the records appear to be in a random order.

[spxdcz]

Yup - what he said.

Check out the manual at:

http://www.mysql.com/doc/M/a/Mathematical_functions.html

[tasistro]

Need a little more convincing than that. I understand that rand() will generate a random number every time. What stops mysql from taking rand as a column position. If rand returns a value and that value is NOT a column name or alias then it could be considered a column position.

Going further if I want to place a function as the order by clause how does mysql know the result is not the order by criteria but the column name/alias/number.

Finally isn't it "evil" to do an order by rand(). I think getting the record count of a table(very fast in mysql), generating a random number(very fast in php) on that interval and fetching a row who's ordinal position is the same random number is much faster. Definetly faster than ordering by an unindexed value [rand()].

Saludos
Gerardo

[[deleted]]

"What stops mysql from taking rand as a column position"

the fact that it says "rand()" and not "1" or "2"

"if I want to place a function as the order by clause how does mysql know the result is not the order by criteria but the column name/alias/number"

Again, becuase it doesn't say "1" or "2" or any other number.

"I think getting the record count of a table(very fast in mysql), generating a random number(very fast in php) on that interval and fetching a row who's ordinal position is the same random number is much faster"

two queries is nearly always slower than one.
it is very difficult to come up with a query that executes slower than it's PHP counterpart. Try it.

[essexboyracer]

i use this:

$result=mysql_query("SELECT title, (ID*0+RAND()) as rand FROM forsale WHERE new=1 ORDER BY rand");

oli

[[deleted]]

that is the 'old' method, newer version let you order by rand() directly.

But this does illustrate how it works; the rand() function is used to create a new virtual column that contains random values.

[[deleted]]

Thanks for all the input. I'll try Vincent's suggestion of rand().

[Anon]

Thanks, this was very helpful. Replaced a bunch of javascript.