Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Wondering how to query for the closest matching

    Lemme explain what I want to do...

    I want to have a collection of images and then get the average color from each and combine the two to get the mid color (I have a pretty good idea on how to do this).

    Then the hard part is this. I want to have a database with a limited amount of different images, with a field of their average color. So like this:
    Code:
    id: 1 | image: flowers.png | avarage_color: #11BD47 (a green color)
    id: 2 | image: panadas.png | avarage_color: #858585 (a grey color)
    id: 3 | image: sunset.jpg | avarage_color: #9360B3 (a purple color)
    And then I have two images that the script is handling: a.png with an average of #F22955 (red) and b.png with an average of #293AF2 (a blue).

    Let's say that A and B theoretically combine into #B71DBF (a purple).

    Is it possible to query mysql so it returns the color closest to #B71DBF (doesn't have to be perfect)?

    If this isn't possible with mysql, any ideas on how to do it?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,558
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    A fun one!

    Yes, I think it could be done.

    It would be best if your colors table had separate R, G, B fields. Just stored as simple INT values.

    And then break your desired color into R, G, B as well and just treat those, too, as INT.

    And then something as simple as
    Code:
    SELECT ( ABS(R - 0xB7) + ABS(G - 0x1D) + ABS(B - 0xBF) ) AS colorDiff, *
    FROM table
    ORDER BY colorDiff 
    LIMIT 1
    What do you think?

    ********

    I think it could even be done if you don't have the R,G,B separate, but it would be a lot more work.

    You'd have to use the MySQL HEX and CONV functions and a lot of care. Ask again if you can't use separate R,G,B values.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,558
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ehhh...I guess it's not that hard:
    Code:
    SELECT (   ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10 )
             + ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10 )
             + ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10 ) ) AS colorDiff, *
    FROM table 
    ORDER BY colorDiff
    LIMIT 1
    The 'B71DBF' would of course come from a PHP (or other language) variable. Or you could split it apart ahead of time in your PHP code and pass the values in as simple ints.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Oooh awesome! Thanks so much!

    Only thing is I get this when I run it.

    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM colors ORDER BY colorDiff LIMIT 1' at line 3
    Thanks for giving me an idea on how to do it!

    EDIT:

    I poked at the query and this got rid of the issue

    Code:
    SELECT (ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10 )
             + ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10 )
             + ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10 ) AS colorDiff), *
    FROM colors 
    ORDER BY colorDiff
    LIMIT 1
    But now it is saying

    Code:
    Incorrect parameters in the call to native function 'ABS'
    Last edited by Kurisvo; 01-05-2012 at 04:37 AM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,558
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Well,you didn't copy my parentheses, so you have the alias in the wrong place, but I goofed as well.

    Let's try again:
    Code:
    mysql> select * from colors;
    +--------+
    | rgb    |
    +--------+
    | 7F3A21 |
    | 3A22ff |
    | A920C0 |
    | FFFFFF |
    +--------+
    4 rows in set (0.00 sec)
    
    mysql> 
     SELECT RGB,
            (   ABS( CONV(SUBSTRING(RGB,1,2),16,10) - CONV(SUBSTRING('B71DBF',1,2),16,10) )
              + ABS( CONV(SUBSTRING(RGB,3,2),16,10) - CONV(SUBSTRING('B71DBF',3,2),16,10) )
              + ABS( CONV(SUBSTRING(RGB,5,2),16,10) - CONV(SUBSTRING('B71DBF',5,2),16,10) )
            ) AS colorDiff
     FROM colors
     ORDER BY colorDiff;
    +--------+-----------+
    | RGB    | colorDiff |
    +--------+-----------+
    | A920C0 |        18 |
    | 3A22ff |       194 |
    | 7F3A21 |       243 |
    | FFFFFF |       362 |
    +--------+-----------+
    4 rows in set (0.00 sec)
    I purposely didn't put in the LIMIT 1 so you could see how reasonable/unreasonable the results are.

    MySQL won't let you use the * wildcard (meaning "all fields") after you use a named field (aliased or not), so if you wanted to use * in that query, you'd need to put it where the very first RGB (in red) is.

    I should note this will be about as efficient as molasses on a winter's day, so hopefully the table you are using it with isn't very large and/or you can limit the choices with a WHERE clause.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Kurisvo (01-05-2012)

  • #6
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Oh wow, that's perfect!!!! Thanks so much!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •