Jump to content

Problem I need help with


Shadowing
 Share

Go to solution Solved by Barand,

Recommended Posts

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 by Shadowing
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Thanks for the reply requinix

 

The problem I'm having isn't how to edit a MySQL data base :P

 

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.

Link to comment
Share on other sites

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 by Shadowing
Link to comment
Share on other sites

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 by Shadowing
Link to comment
Share on other sites

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 by Shadowing
Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.