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"); ?> Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/ 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. Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302836 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 Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302846 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. Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302853 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']; ?> Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302947 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? Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302949 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? Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302952 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"); ?> Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302953 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? Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302954 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? Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302962 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 Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302983 Share on other sites More sharing options...
AyKay47 Posted January 1, 2012 Share Posted January 1, 2012 Quote 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. Link to comment https://forums.phpfreaks.com/topic/254118-random-field-update/#findComment-1302995 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.