Problem I need help with

[Shadowing]

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

[requinix]

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_level

I 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.

[Shadowing]

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.

[Shadowing]

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.

[Shadowing]

The only real solution I came up with is using 25 random functions with stops so its as efficient as possible.

[Shadowing]

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

[Shadowing]

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

[Shadowing]

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

[Barand]

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

[Shadowing]

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