Shadowing Posted August 16, 2015 Share Posted August 16, 2015 (edited) Hey guys I have a script I need to build and I'm trying to figure out the most efficient way to do it. Say I have 1000 rows in mysql (planets). Each row has an auto increment id I need to cycle through each row and add what level the planet is(1-25). I also need the more higher level a planet is to be more rare in the Galaxy. After coming up with two ideas both not efficient enough. I woke up today with a new idea but I need some help in completing the idea of it. Since each row has its own Id. I'm thinking that can be used to determine rareness of the level. Hoping a math guy here at phofreaks knows of a way I can play on the math with the id. Thanks Edited August 16, 2015 by Shadowing Quote Link to comment Share on other sites More sharing options...
requinix Posted August 16, 2015 Share Posted August 16, 2015 Adding the level should be as simple as UPDATE table_with_the_planets SET whatever_thing_youre_updating = whatever_thing_youre_updating + column_with_the_levelI have no idea what any of this has to do with rarity, or really why you need to "add what level the planet is" in the first place. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 Thanks for the reply requinix The problem I'm having isn't how to edit a MySQL data base It's figuring out a level of a planet so when I generate 10,000 planets or more each planet is given a level (1-25) After all 10,000 planets are inserted into the database. The end result will be that there is fewer level 2's than level 1's and so forth. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 If I can come up with a solution that doesn't use random functions that be best. Wouldn't even need to assign it to the row. One kinda idea is using the first digits of a number with a T chart but idk. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 The only real solution I came up with is using 25 random functions with stops so its as efficient as possible. Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 (edited) I figured out a solution. Using the last 3 digits of every Id I can say every 100 is level 25 planets Every number between 99 and 97 is level 24 planets So 1% of planets would be lvl 25 and 2% would be lvl 24 etc Whatever I want set each level too. That's a simple solution. Idk why it took me so long to think that idea up Edited August 16, 2015 by Shadowing Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 (edited) Actually that idea doesn't work either. Cause then there will be like 20 level 10's close together in the galaxy. So I guess on that principal alone it has to be done with random functionsfunctions I'll just do that I guess. Unless someone has another idea Edited August 16, 2015 by Shadowing Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 (edited) A new idea has arose. $planets = 10,000; $rate_pool = array( 25 => .01, 24 => .02, ); $level_pool = array( 25 => $planets * $rate_pool[25], 24 => $planets * $rate_pool[24] ); I'll random pull a key from $level_pool then i'll subtract from $level_pool and when one of them are empty I remove the element; Edited August 16, 2015 by Shadowing Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 16, 2015 Solution Share Posted August 16, 2015 (edited) This method allocates 15% of the planets to level 1, 15% of the remaining planets to level 2, and so on. The results for 10,000 planets were Level 25 : 30 Level 24 : 36 Level 23 : 42 Level 22 : 49 Level 21 : 58 Level 20 : 68 Level 19 : 81 Level 18 : 95 Level 17 : 111 Level 16 : 131 Level 15 : 154 Level 14 : 181 Level 13 : 213 Level 12 : 251 Level 11 : 295 Level 10 : 347 Level 9 : 409 Level 8 : 481 Level 7 : 565 Level 6 : 665 Level 5 : 783 Level 4 : 921 Level 3 : 1083 Level 2 : 1275 Level 1 : 1676 TOTAL :10000 Time taken : 17.430 seconds The code <?php include("db_inc.php"); // defines HOST, USERNAME and PASSWORD try { $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); } catch(Exception $e) { die("DB connection error"); } $t1 = microtime(1); // start the clock /******************************************************************** * CREATE THE TEST DATA planet TABLE (uncomment if required) ********************************************************************* $db->query("DROP TABLE IF EXISTS planet"); $sql = "CREATE TABLE planet ( planet_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, planetname VARCHAR(30), level INT )"; $db->query($sql); $sql = "INSERT INTO planet (planetname) VALUES (?)"; $stmt = $db->prepare($sql); $stmt->bind_param('s', $pn); for ($i=1; $i<=10000; $i++) { $pn = "Planet $i"; $stmt->execute(); } $stmt->close(); *********************************************************************/ /******************************************************************** * define the %age distribution of the levels *********************************************************************/ $distrib_pcent = 15; /******************************************************************** * how many planets (N)? *********************************************************************/ $sql = "SELECT COUNT(*) FROM planet"; $res = $db->query($sql); list($N) = $res->fetch_row(); $planet_levels = []; // define empty array $planets = range(1,$N); shuffle($planets); // planet ids in random order /******************************************************************** * fill planet_level array with levels to be applied to each planet *********************************************************************/ $key = 0; $n = $N; for ($level = 1; $level <= 25; $level++) { $k = floor($n * $distrib_pcent / 100); // how many planets get this level? $n -= $k; // $n = remaining planets if ($n < 0) { die( $level . ' level reached<br>'); // check we don't run out of planets } for ($i=0; $i<$k; $i++) { $planet = $planets[$key++]; // next random planet id $planet_levels[$planet] = $level; // set its level } } /****************************************************************************** * update the planets with their assigned levels *******************************************************************************/ $db->query("UPDATE planet SET level=1"); // set default level $sql = "INSERT INTO planet (planet_id, level) VALUES (?,?) ON DUPLICATE KEY UPDATE level = VALUES(level)"; $stmt = $db->prepare($sql); $stmt->bind_param('ii',$pid,$level); foreach ($planet_levels as $pid=>$level) { if ($level != 1) $stmt->execute(); // EDIT: only need update those != 1 } $t2 = microtime(1); // stop the clock; /****************************************************************************** * check the results *******************************************************************************/ $sql = "SELECT level, COUNT(*) as tot FROM planet GROUP BY level ORDER BY tot"; $res = $db->query($sql); $tdata = ''; $totalPlanets = 0; while (list($lev, $tot) = $res->fetch_row()) { $tdata .= sprintf("Level %2d : %4d\n", $lev, $tot); $totalPlanets += $tot; } ?> <html> <head> <title>Planet levels</title> </head> <body> <pre> <?php echo $tdata; printf("<b></b>%8s :%5d</b> Time taken : %6.3f seconds\n", 'TOTAL', $totalPlanets, $t2-$t1); ?> </pre> </body> </html> Edited August 16, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
Shadowing Posted August 16, 2015 Author Share Posted August 16, 2015 That's an amazing idea Barand on generating the array. Didn't consider taking a new percentage of the stars left on each level when building the array. cheers 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.