Page 1 of 1
RAND() mysql statement
Posted: Sat Jan 25, 2003 11:20 pm
by stickman373
What's wrong with this statement?
Code: Select all
$query = mysql_query("SELECT link FROM downloads WHERE version='".$version."' ORDER BY RAND() LIMIT 1");
I run this and always get the same entry for a link. There are 3 different rows which match the criteria, but I always get the same row everytime i run the script.....

Posted: Sun Jan 26, 2003 4:42 am
by gyardleydn
[Deleted my first few tries. This seems a better test]
I ran it and it works on my setup. If you run this several times using the same value of version does:
A) The md5 text change?
B) The random numbers change?
C) The row with the lowest random number change?
Code: Select all
<?php
/*Connect command etc */
$query = "SELECT link, RAND() FROM downloads WHERE version='".$version."'";
$result = mysql_query($query);
echo $query, '<BR>';
echo md5(time()), '<BR>';
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
printf ("Link: %s Random: %s <BR>", $row[0], $row[1]);
}
mysql_free_result($result);
?>
For a quick fix RAND(UNIX_TIMESTAMP()) may do the trick, if you don't mind simultaneous viewers seeing the same rows but you shouldn't need to set a seed
Posted: Sun Jan 26, 2003 9:01 am
by stickman373
the md5 changes and so do the random #'s but, it seems the random #'s dont change enough to actually change the order from highest to lowest
Posted: Sun Jan 26, 2003 7:58 pm
by gyardleydn
So there is nothing with your algorithm. In your version of MySQL its pseudorandom generator is insufficiently random, at least for small sets. In 4.01 RAND() initialization was changed, so I would mark this area for reexamination should you or your provider ever upgrade.
Also here's a user note on MySQL doc site
+++++++++++++++++
Posted by Nick Gaugler on Tuesday November 19 2002, @10:21am
As of MySQL 3.23.52, MySQL changed the way
RAND() functions, such that you MUST supply a
SEED to get an actual random number. If you do
not, each new connection will return close to the
same number as the previous new connection. An
example to test this would be to run the follow
command multiple times in a row. mysql -u
username -p -e'select rand()' A basic way to seed
this would be to run RAND(NOW()).
+++++++++++++++++
In the mean time you could either use
1.
RAND(UNIX_TIMESTAMP()) or
RAND(UNIX_TIMESTAMP()+$uniqueUserId) where $uniqueUserId could be the users login ID or their IP address with the “.” removed
2.
Collect the complete set and use php’s rand() function
3.
Use some kind of subkey that is sequential for each version. Use the Max() SQL function, and then the php rand() function to generate the subkey you will use in the query.