Store all RGB values in a database

Question

What's the best way to store something like this? (one time only) in a MYSQL database? It needs to insert all RGB values in a table.

$colors = colourArray(0, 255, $con);
function colourArray($start, $number, $con) {
    foreach(range($start,$number) as $r) {
        foreach(range($start,$number) as $g) {
            foreach(range($start,$number) as $b) {
                mysqli_query($con, 'INSERT INTO rgbcolors (r,g,b) VALUES ('.$r.','.$g.','.$b.')');
            }
        }
    }
}

This works, but it takes a very long time to finish with 1 query at a time..

Does someone know a neater way?

Answer 1

You can insert more then one set of values at a time.

$colors = colourArray(0, 255, $con);
function colourArray($start, $number, $con) {

    $colors = array();

    foreach(range($start,$number) as $r) {
        foreach(range($start,$number) as $g) {
            foreach(range($start,$number) as $b) {
                $colors[] = '('.$r.','.$g.','.$b.')';
            }
            // this could be moved to the parent foreach if you have enough memory to allocate to this.
            mysqli_query($con, 'INSERT INTO rgbcolors (r,g,b) VALUES ' . implode(',', $colors));
            $colors = array();
        }
    }

}

Answer 2

You could append the INSERTs into one long string. Make sure to terminate each INSERT with a semicolon.

$sql = $sql . 'INSERT INTO rgbcolors (r,g,b) VALUES ('.$r.','.$g.','.$b.');';

Then after the loop:

mysqli_query($con, $sql);

However, there is a maximum string length that can be sent to the MYSQL database. See this SO article for more info. So you will probably need to send the data every, maybe, every 1,000 or 10,000 iterations. 10,000 will be a 2 to 3 MB string.

Answer 3

You can batch your inserts into single statements.

INSERT INTO rgb_colors VALUES(r,g,b),(r,g,b),(r,g,b)

I modified your function a bit to remove the $con stuff since I wasn't using it here, but here is a single INSERT (probably WAY too big) with all the colors in it.

In the real world you'd want to create batches of ~20-30 at a time.

See the results here: http://codepad.org/dkuZpt10

<?php

colourArray(0, 255);

function colourArray($start, $number) {
    echo "INSERT INTO rgbcolors VALUES \n";
    foreach(range($start,$number) as $r) {
        foreach(range($start,$number) as $g) {
            foreach(range($start,$number) as $b) {
                echo "($r,$g,$b),\n";
            }
        }
    }
}

?>