Jump to content

# Problem I need help with

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
##### 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.
##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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

##### 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
##### 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
##### 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
##### 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
##### Share on other sites

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

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

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.

×
×
×
• 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.