Shadowing Posted December 31, 2011 Share Posted December 31, 2011 Im wanting to update a random field in a table and only update the fields that are empty is this the best way to do this. im not sure if WHERE id = ' ' will only choose a blank field <?php mysql_query("UPDATE systems SET id= '".($_SESSION['user_id'])."' , owner = '".($_SESSION['user_id'])."' WHERE id = '' ORDER BY RAND() LIMIT 1"); ?> Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted December 31, 2011 Share Posted December 31, 2011 It will grab fields with an empty id. Order by rand is not a good idea. You can google why. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted December 31, 2011 Author Share Posted December 31, 2011 I read that someone did a test on a million rows the differance was .0010 Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted December 31, 2011 Share Posted December 31, 2011 well it really depends on how big your data set is. For larger result sets, order by rand() is very inefficient compared to alternatives, for smaller sets, you can get away with it but I still don't recommend it. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted January 1, 2012 Author Share Posted January 1, 2012 this is what i came up with from googling another way to do this cause i would like to keep performance in mind. anychance you can help me out with generating a random id <?php // search how many rows there are $sql = "SELECT * AS id FROM systems"; $query = mysql_query($sql) or die(mysql_error()); $fetch = mysql_fetch_row($query); // add the number of rows to a variable $rows = $fetch['id']; //I dont know how to generate a random number to use in the next query $sql = "SELECT id FROM systems LIMIT $random_number, 1 "; $query = mysql_query($sql) or die(mysql_error()); $fetch = mysql_fetch_array($query); $id = $fetch['id']; ?> Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 1, 2012 Share Posted January 1, 2012 rand what is the point of the first query? Quote Link to comment Share on other sites More sharing options...
Shadowing Posted January 1, 2012 Author Share Posted January 1, 2012 dont i need to find out how many rows their are first? i would need to add in Where id = '' so it only counts rows that have a blank id field then im finding a random number according to how many rows there is? Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 1, 2012 Share Posted January 1, 2012 on second thought, after looking at the OP again, the original sql statement that you have will work, if you are only worried about updating one random field. <?php mysql_query("UPDATE systems SET id= '".($_SESSION['user_id'])."' , owner = '".($_SESSION['user_id'])."' WHERE id = '' ORDER BY RAND() LIMIT 1"); ?> Quote Link to comment Share on other sites More sharing options...
Shadowing Posted January 1, 2012 Author Share Posted January 1, 2012 what a relief ha plus this script is only ran when people start or reset their accounts. so that has to help alot right? Quote Link to comment Share on other sites More sharing options...
trq Posted January 1, 2012 Share Posted January 1, 2012 This whole idea smells bad. Why exactly are you updating some random row in the first place? Quote Link to comment Share on other sites More sharing options...
Shadowing Posted January 1, 2012 Author Share Posted January 1, 2012 on account setup a player gets assigned a planet in the table so I randomly find a planet that isnt assigned to anyone and assign it to them by adding their player id to it. Im making a physical map so the planet cant just be created. Has to be chosen Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 1, 2012 Share Posted January 1, 2012 what a relief ha plus this script is only ran when people start or reset their accounts. so that has to help alot right? well, yeah I suppose, I'm not a fan of order by rand(), however technically in this case it will work and shouldn't affect efficiency. 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.