Store RGB colors in MySQL. Better as char or int?

Question

I'm using PHP to query CSS settings from a MySQL database, which is then echoed into a CSS stylesheet. Sample snippet is below:

<?php
    $connect = //connect string
    $query = ($connect, "SELECT bgcolor, font FROM displays WHERE name = 'mySettings'");
    while ($row = mysqli_query($query)){
        $bgcolor = $row[bgcolor];
        $font = $row[font];
    }
    echo '
        body {
            background-color: #'.$bgcolor.';
            font: '.$font.';
        }
    ';
?>

The table "displays" has one column for each CSS property that's set. Each row represents a set of color/font/padding/margin settings the user has saved.

The column "font" is data type varchar(50). What data type should "bgcolor" be? CSS wants to see a hex value, but there's no hex datatype in MySQL. The logical choice would be to store the value as type int and use the HEX() function in the SELECT statement.

Then again, might it be easier and/or use less space to just store it as a char(6)? PHP is just treating as a string, not an actual number. The hex value won't have any mathematical functions applied to it; it's just pasted into the CSS.

In a case like this, is there any preference between storing the value as an int or a char?

Answer 1

This is what I did:

Store red, green and blue in different columns. Each as TINYINT. Since tinyint is between 0..255 (unsigned), not a single bit will be wasted, and you can filter results as you wish(reds, pastels, dark tones etc).

Answer 2

Expanding on my comment:

Since the values are for display purposes only, I would store the CSS attribute value entirely, whatever it may be, as a string in the database, e.g.:

#ab382d

This would eliminate the need to have the '#' sitting there in your CSS, so you could then potentially store values such as

• transparent
• blue
• rgb(100, 100, 100)
• (or any other valid background-color attribute value)

without needing to know how to render it.

EDIT: You may also want to think about a caching mechanism to reduce the number of hits to the database for stylesheet information that probably doesn't change too often.

Answer 3

24-bit and 32-bit RGB values can be expressed as 32-bit integers, so for efficient storage you can use int, but you'll always need to decode it into something human-readable or CSS-readable when loading and saving.

Psuedocode:

 UInt32 rgba  = color.R | ( color.G << 8 ) | ( color.B << 16 ) | ( color.A << 24 );
 Color  color = Color.FromRgba( rgba & 0xFF, ( rgba >> 8 ) & 0xFF, ( rgba >> 16 ) & 0xFF, ( rgba >> 24 ) & 0xFF );