Choosing a random user.

[Dragon Blade]

Hey there, I need help. I want my query underneath to randomly choose any members with the field draw +1

Any help will be appreciated.

 

 

$draw=mysql_query("SELECT * FROM userstatistics WHERE draw > 0 LIMIT 1");
$sql=mysql_fetch_array($draw);
$draw2 = mysql_query("SELECT * FROM users WHERE userid = {$sql['userid']}");
$sql2=mysql_fetch_array($draw2);

[KyleMassacre]

well what you can do is:

1. Query the total number of users and add that in your "LIMIT" like so LIMIT $count, 1 (recommended)
   
2. Do an ORDER BY RAND() LIMIT 0, 1 (not recommended but easier)
   

 

for the first one for some reason I'm confused right now cause I'm a little bit under the weather but the $count,1 may need to be flip-flopped like 1, $count

Edited August 17, 2013 by KyleMassacre

[Venom]

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` ORDER BY RAND() LIMIT 1");

That is one way of doing it.

 

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` && `KEY_FIELD`='".(mt_rand(1,mysql_num_rows(mysql_query("SELECT NULL FROM `userstatistics`"))))."'");

That is another, of course the first is a lot simpler. The second just generates a random number between 1 and the number of rows, and grabs that specific one.

There may be more optimized ways of doing it though, I just threw these together.

[KyleMassacre]

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` ORDER BY RAND() LIMIT 1");

That is one way of doing it.

 

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` && `KEY_FIELD`='".(mt_rand(1,mysql_num_rows(mysql_query("SELECT NULL FROM `userstatistics`"))))."'");

That is another, of course the first is a lot simpler. The second just generates a random number between 1 and the number of rows, and grabs that specific one.

Yep, pretty much what I was saying but the rand() is a bit more intensive on the server

[SRB]

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` ORDER BY RAND() LIMIT 1");

That is one way of doing it.

 

$draw = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` && `KEY_FIELD`='".(mt_rand(1,mysql_num_rows(mysql_query("SELECT NULL FROM `userstatistics`"))))."'");

That is another, of course the first is a lot simpler. The second just generates a random number between 1 and the number of rows, and grabs that specific one.

There may be more optimized ways of doing it though, I just threw these together.

Would be faster to calculate this at PHP level, than the above:

 

$store = array();
$sql   = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` > 0 LIMIT 1");
if (mysql_num_rows($sql) > 0)
{
   while ($user = mysql_fetch_assoc($sql))
   {
       $store[] = $user['userid'];
   }
}
shuffle($store);
$winner = $store[ mt_rand(0, ($store - 1)) ];
echo 'Winner is: ' . $winner;

[Alan]

Think scale; we know ORDER BY RAND() is not optimal, neither is reading each row into PHP so ...

// compute number of rows in table
$rs = mysql_query("SELECT COUNT(draw) FROM userstatistics WHERE draw > 0");
$row = mysql_fetch_row($rs);
$count = $row[0];

// pick a random number from 0 to $count - 1
$offset = mt_rand(0, $count - 1);

// grab the user id from the userstatistics
$rs = mysql_query("SELECT userid FROM userstatistics WHERE draw > 0 ORDER BY userid LIMIT $offset, 1");
$row = mysql_fetch_row($rs);
$userid = $row[0];

// finally, read the row from the users table
$rs = mysql_query("SELECT * FROM users WHERE userid = $userid");
$user = mysql_fetch_assoc($rs);

You could probably combine the last two operations with a JOIN, however whether you do or not will be determined by the database engine (MyISAM vs InnoDB) and the number of rows we are looking at it.

There is a possible race condition here, however careful checking of each result can mitigate that down to acceptable levels.

[HauntedDawg]

Would be faster to calculate this at PHP level, than the above:

 

$store = array();
$sql   = mysql_query("SELECT * FROM `userstatistics` WHERE `draw` > 0 LIMIT 1");
if (mysql_num_rows($sql) > 0)
{
   while ($user = mysql_fetch_assoc($sql))
   {
       $store[] = $user['userid'];
   }
}
shuffle($store);
$winner = $store[ mt_rand(0, ($store - 1)) ];
echo 'Winner is: ' . $winner;

Limit 1, would result in 1 entry being returned.

@Alan, 3 sql queries? Uhm, no, please.

 

$draw = mysql_query('SELECT `usr`.`userid`, `usr`.`username` FROM `users` `usr` LEFT JOIN `userstatistics` `draw` ON `usr`.`userid` = `draw`.`userid` WHERE `draw`.`draw` != 0 AND `draw`.`userid` => FLOOR(1 + RAND() * (SELECT MAX(`userid`) FROM `users`)) LIMIT 1');
$draw = mysql_fetch_assoc($draw);
echo 'Winner is: '.$draw['userid'].' :: '.$draw['username'];

 

Tested on a dummy table with 20k rows into the users, and 15k into the userstatistics, results returned within 0.0012 seconds.

[Alan]

Think scale

20K rows .. bless.

[SRB]

Limit 1, would result in 1 entry being returned.

You're right. Slight oversight on my part, but alas, I do not have a problem with this, so I can afford to slip here.

 

20K rows .. bless.

I LOL'd.

At the last company I worked for, I had to cross reference 2 tables. One slightly exceeding 20k while the other was over 1.2 million. Yes, that's a pain in the ass!

[Alan]

SELECT ... JOIN ... (SUBQUERY ...) -- Slow for many rows, stalls multiple tables.

vs

SELECT ... ORDER BY RAND() -- Slow for many rows, stalls the table.

vs

SELECT ...

SELECT ...

SELECT ... -- Multiple context switches, suffers from race condition.

vs

CALL ... -- Using the tri-select, works well, fast and doesn't suffer from race conditions

Kinda makes you think that there might be a far better solution that is independent of database, of table design, indeed of number of rows.

[HauntedDawg]

20k row's was to test, where as I highly doubt the OP has over 20k users!

Previous company I worked for had 9.6million entries into the logs table, 600k of user data. Had to cross reference those.

Now, as for the OP. If he used any of our methods here, he would notice no change at all, because.. as I said, i doubt he has over 20k users.

[Dragon Blade]

Thank you guys for helping me out.

[SRB]

Missing the point still. May not have 20k users now, but who is to say it won't ever have? Emphasis is still on "scalability".

Having your answer as the only right one seems to be a habit of yours lately. Think I am going to nickname you Cavell.

 

20k row's was to test, where as I highly doubt the OP has over 20k users!

Previous company I worked for had 9.6million entries into the logs table, 600k of user data. Had to cross reference those.

Now, as for the OP. If he used any of our methods here, he would notice no change at all, because.. as I said, i doubt he has over 20k users.

[Seker]

Think I am going to nickname you Cavell.

/snicker

/2short