Jump to content
MakeWebGames

Choosing a random user.


Dragon Blade

Recommended Posts

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);
Link to comment
Share on other sites

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 by KyleMassacre
Link to comment
Share on other sites

$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.

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

$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;
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...