KojoSlayer KojoSlayer - 7 months ago 33
SQL Question

MySQL insert random items up to 10,000 times?

Not sure how this is possible or if its an easy solutions I'm not sure of or even the name of it.

I'm looking to insert 10,000 rows into a database with three columns for a "map" area of 100x100:

-----------------
| x | y | type |
-----------------
| 1 | 1 | 1 |
-----------------
| 1 | 2 | 1 |
-----------------
| 1 | 3 | 2 |
-----------------
| 1 | 4 | 3 |
-----------------
| 1 | 5 | 4 |
-----------------
| 1 | 6 | 4 |
-----------------
| 1 | 7 | 2 |
-----------------
| 1 | 8 | 2 |
-----------------
| 1 | 9 | 1 |
-----------------
| 2 | 1 | 2 |
-----------------
| 2 | 2 | 1 |


Basically X column goes up to 100 Y column goes up to 100 so there will be X:1 Y:1 and X:100 Y:100 and all of the variations (10,000) Along with this the type column needs to be a random number between 1 and 4. (this would be ideal if I could set a percentage of each number e.g 1 25% of the 10,000 2 is 15%, 3 is 40% and 4 is 20%).

Is there a way to do this with MySQL to generate this some how rather than writing up an insert for 10,000 rows? Or would I be best off writing some form of PHP script to execute and insert this?

Thanks!

Answer
<?php
$values = [];

for ($x = 1; $x <= 100; $x++)
{
    for ($y = 1; $y <= 100; $y++)
    {
        $rand = mt_rand(1, 100);

        switch (true)
        {
            case ($rand <= 25):  $i = 1; break;
            case ($rand <= 40):  $i = 2; break;
            case ($rand <= 80):  $i = 3; break;
            case ($rand <= 100): $i = 4; break;
        }

        $values[] = sprintf("(%d, %d, %d)", intval($x), intval($y), intval($i));
    }
}

$sql = "Insert Into table (x, y, type) Values" . implode(',', $values);

echo $sql;
Comments