Help with RAND()

[slevytam]

Hello,

Could anyone tell me why this isn't generating a random record?

$queryrandom = "select * from users, usersmedia WHERE users.sex='$iseeking' AND users.seeking='$isex' AND users.ID=usersmedia.ID AND usersmedia.importance='1' AND usersmedia.type='1' order by RAND() LIMIT 1";

Thanks,

--SL

[_0_05_]

i THINK that rand() uses a decimal number....

check php.net

[slevytam]

Thanks for your reply. I have already checked out php.net

This is what I found.

Description
int rand ( [int min, int max])

If called without the optional min, max arguments rand() returns a pseudo-random value between 0 and RAND_MAX. If you want a random number between 5 and 15 (inclusive), for example, use rand (5, 15).

In older versions of PHP, you had to seed the random number generator before use with srand(). Since 4.2.0 this is no longer necessary.

Note: In versions before 3.0.7 the meaning of max was range. To get the same results in these versions the short example should be rand (5, 11) to get a random number between 5 and 15.

See also srand(), getrandmax(), and mt_rand().

User Contributed Notes
rand

hartmut at six dot de
25-Aug-2000 07:43

In MySQL 3.23 or greater, the following query selects a random row :

SELECT * FROM tablename ORDER BY RAND() LIMIT 1

---

Still don't know why I always return the same record.

Thanks,

--SL

[nnichols]

Try running the query without the "LIMIT 1" just to check that the query is returning more than one row. The query you have should give you one random record that satisfies all your criteria.

When you refresh it should show you the same number of results but in a different order.

[slevytam]

Thanks for your help,

I ran the query without limit one and received 30 results.

Every refresh produces records in the exact same order.

Any ideas,

--sL

[nnichols]

What version of MySQL are you running??

[slevytam]

Hello,

I am running:
PHP: 4.2.3
MYSQL: 3.23.54

This works perfectly in MySQL but not from a php page.
select * from users, usersmedia WHERE users.sex='Male' AND users.seeking='Female' AND users.ID=usersmedia.ID AND usersmedia.importance='1' AND usersmedia.type='1' ORDER BY RAND() LIMIT 1;

Still can't figure out why...

--SL

[slevytam]

I've done some messing around and found something that might help

If I do this:
select * from users order by rand(now());
There is no problem.

As soon as I do this
select * from users where sex='Male' order by rand(now());
It doesn't work!

Please help!

--sL

[ednark]

i think you are getting confused between mysql and php

mysql has a RAND() function

http://www.mysql.com/doc/en/Mathematical_functions.html

and php also has a rand function

http://php.net/rand

they do drastically different things

This uses the SQL rand:

<?php
/// this should give 20 random results from all the rows in sql
$sql = "SELECT * FROM table1 ORDER BY RAND() LIMIT 20";
?>

this is the php rand

<?php
// this will return anywhere from 1 to 20 results, same results each time
$limit_rand = rand(1,20);
$sql = "SELECT * FROM table1 LIMIT {$limit_rand}";
?>

[slevytam]

Unfortunately, I wasn't getting confused. It looks like a bug with mysql.

Check out:
http://groups.google.com/groups?q=mysql+rand&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b2bspq%246gh%241%40FreeBSD.csie.NCTU.edu.tw&rnum=9

Thanks for your help,

--SL

[ednark]

your right, sorry... after looking over your code.. you were using it perfectly fine 🙂

but i didn't notice as i haven't ran into this bug before... interesting