plznty Posted October 23, 2010 Share Posted October 23, 2010 How would I make it so that for every set of data where a fieldname is 1+ it picks a random one out. For example User Hit Luke 0 Peter 1 Alex 3 Peter 1 For every value where Hit is 1 or over it selects out of the query results a random username. Quote Link to comment Share on other sites More sharing options...
joesaddigh Posted October 23, 2010 Share Posted October 23, 2010 SELECT column FROM table WHERE Hit >0 ORDER BY RAND() LIMIT 1 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 23, 2010 Share Posted October 23, 2010 Before you do that, Google 'mysql why order by rand() is bad'. Quote Link to comment Share on other sites More sharing options...
joesaddigh Posted October 23, 2010 Share Posted October 23, 2010 Is there a good alternative? Quote Link to comment Share on other sites More sharing options...
wannabephpdude Posted October 24, 2010 Share Posted October 24, 2010 I would definitely have a unique id for any table [Edit] when comes to storing users for sure. If you can work that out then the following will be helpful. Take a look at solution 2 as opposed to using rand() within your query. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 24, 2010 Author Share Posted October 24, 2010 so how could I gather users who have 1<= and then select a random id from the results? Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 24, 2010 Share Posted October 24, 2010 Before you do that, Google 'mysql why order by rand() is bad'. Did you do this ^^^? Reading some of the results will help you make the decision whether you want to use ORDER BY RAND() or not. There are also better solutions in each explanation of the downfalls. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 24, 2010 Author Share Posted October 24, 2010 I've got about 1000 results to pick from maybe 1/2 of which will needed to be called. Is this amount of data okay. Yes i did read Thanks Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 24, 2010 Share Posted October 24, 2010 I'd have to say with that small of a record set, you'll be OK. Just remember that you used ORDER BY RAND() there, and if the query starts to bog down as the table grows, change to a more efficient method. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 24, 2010 Author Share Posted October 24, 2010 Alright thanks! Quote Link to comment Share on other sites More sharing options...
plznty Posted October 24, 2010 Author Share Posted October 24, 2010 Reopened. How do i write this out I keep getting Resource id #2 output. Thanks Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 How would I write this. (bump) (was off first page) Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 25, 2010 Share Posted October 25, 2010 Post the code you're using to query the DB and display the result. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 I keep getting Resource id #2 output. Quote Link to comment Share on other sites More sharing options...
litebearer Posted October 25, 2010 Share Posted October 25, 2010 As Pika said... Post the code you're using to query the DB Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 oops 2x post. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 $query = mysql_query("SELECT * FROM users WHERE clicks >0 ORDER BY RAND() LIMIT 1;"); echo $query; echo $row['email']; Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 25, 2010 Share Posted October 25, 2010 There's an error in the query syntax (semicolon inside the quotes), and you need to actually do something with the query result before you can use the values it returns. You also had nothing in place to check to see if the query succeeded. $query = "SELECT * FROM users WHERE clicks > 0 ORDER BY RAND() LIMIT 1"; if( $result = mysql_query($query) ) { $array = mysql_fetch_assoc($result); echo $row['email']; } else { echo '<br>Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'; } Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 it displays nothing. This is doing my head in. Thanks for helping though <?php $connect = mysql_connect("localhost", "dbname", "pass") or die(mysql_error()); mysql_select_db("db") or die(mysql_error()); $query = "SELECT * FROM users WHERE clicks > 0 ORDER BY RAND() LIMIT 1"; if( $result = mysql_query($query) ) { $array = mysql_fetch_assoc($result); echo $row['id']; } else { echo '<br>Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'; } ?> Produces nothing Quote Link to comment Share on other sites More sharing options...
mentalist Posted October 25, 2010 Share Posted October 25, 2010 hmmm $array = mysql_fetch_assoc($result); echo $row['id']; i'm sure your issue lies here, something to do with the names of variables... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 25, 2010 Share Posted October 25, 2010 Change echo $row['id']; to echo $row['id'] . ' id should be here'; To see if if the code is at least entering that part of the conditional. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 <?php $connect = mysql_connect("localhost", "", "") or die(mysql_error()); mysql_select_db("") or die(mysql_error()); $query = "SELECT * FROM users WHERE clicks > 0 ORDER BY RAND() LIMIT 1"; if( $result = mysql_query($query) ) { $array = mysql_fetch_assoc($result); echo "hello".$row['id']; } else { echo '<br>Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>'; } ?> Does display "hello" so something to do with $row variable etc Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 25, 2010 Share Posted October 25, 2010 D'oh! Change $array = mysql_fetch . . . to $row = mysql_fetch . . . Sorry about that. Quote Link to comment Share on other sites More sharing options...
plznty Posted October 25, 2010 Author Share Posted October 25, 2010 D'oh! Change $array = mysql_fetch . . . to $row = mysql_fetch . . . Sorry about that. Your a god. Thanks! Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted October 25, 2010 Share Posted October 25, 2010 If you put this code on a live production server, you'll probably want to change the error reporting that's in the else{} to something generic like "Sorry, there was a database error." and log the actual error in a logfile instead, but that's another subject. 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.