Dragon Blade Posted August 17, 2013 Share Posted August 17, 2013 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); Quote Link to comment Share on other sites More sharing options...
KyleMassacre Posted August 17, 2013 Share Posted August 17, 2013 (edited) well what you can do is: Query the total number of users and add that in your "LIMIT" like so LIMIT $count, 1 (recommended) 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 Quote Link to comment Share on other sites More sharing options...
Venom Posted August 17, 2013 Share Posted August 17, 2013 $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. Quote Link to comment Share on other sites More sharing options...
KyleMassacre Posted August 17, 2013 Share Posted August 17, 2013 $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 Quote Link to comment Share on other sites More sharing options...
SRB Posted August 18, 2013 Share Posted August 18, 2013 $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; Quote Link to comment Share on other sites More sharing options...
Alan Posted August 18, 2013 Share Posted August 18, 2013 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. Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 19, 2013 Share Posted August 19, 2013 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. Quote Link to comment Share on other sites More sharing options...
Alan Posted August 19, 2013 Share Posted August 19, 2013 Think scale 20K rows .. bless. Quote Link to comment Share on other sites More sharing options...
SRB Posted August 19, 2013 Share Posted August 19, 2013 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! Quote Link to comment Share on other sites More sharing options...
Alan Posted August 19, 2013 Share Posted August 19, 2013 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. Quote Link to comment Share on other sites More sharing options...
HauntedDawg Posted August 20, 2013 Share Posted August 20, 2013 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. Quote Link to comment Share on other sites More sharing options...
Dragon Blade Posted August 31, 2013 Author Share Posted August 31, 2013 Thank you guys for helping me out. Quote Link to comment Share on other sites More sharing options...
SRB Posted August 31, 2013 Share Posted August 31, 2013 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. Quote Link to comment Share on other sites More sharing options...
Seker Posted September 1, 2013 Share Posted September 1, 2013 Think I am going to nickname you Cavell. /snicker /2short Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.