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?

Just for fun, i would like to make some kind of groups.. orderned by colour like "get all green variations from table".. with a kind of scope of course. — NoBo, Jun 09 '15 at 19:31
^ that... is another question. You say that after you posted your question and given answers below. You shouldn't be asking that after people posted answers. — Funk Forty Niner, Jun 09 '15 at 19:32
i know.. but i am not asking how to do that.. it's an answer for bmscomp.. — NoBo, Jun 09 '15 at 19:33
@NoBo Ok. But when responding to another person's comment, you need to call them out directly using the @ symbol just like I did here, followed by the first letter of that person's name. I for one took your comment as an additional question. — Funk Forty Niner, Jun 09 '15 at 19:35
@NoBo you've been given answers below. Have any of those worked for you or not? You need to let them know either way. They have no idea whether you seen or used it and if one provided a solution for your problem. — Funk Forty Niner, Jun 09 '15 at 19:51
@NoBo That's great, am glad to see you've gotten a solution; Welcome to Stack, *cheers* — Funk Forty Niner, Jun 09 '15 at 20:00

cmorrissey · Accepted Answer · 2015-06-09T19:41:37.717

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();
        }
    }

}

edited Jun 09 '15 at 19:41

answered Jun 09 '15 at 19:30

cmorrissey

8,493
2
23
27

This is better than mine as it will create a shorter string. – jwatts1980 Jun 09 '15 at 19:34
which will probably blow up by exceeding max_allowed_packet. you're talking about a ~80 megabyte query string. at least do the query at the end of the second loop, so you're only building/running 2^16 queries, instead of 2^24. – Marc B Jun 09 '15 at 19:34
Thank you guys for the answers, works like a charm! =) – NoBo Jun 09 '15 at 19:53

score 0 · Answer 2 · edited May 23 '17 at 12:22

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.

score -1 · Answer 3 · answered Jun 09 '15 at 19:35

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";
            }
        }
    }
}

?>

Store all RGB values in a database

3 Answers3