Home / Questions / 54012
Accepted
Edwin V
Asked: 2020-03-09 12:12:02 +0800 CST 2020-03-09 12:12:02 +0800 CST

Create search by color PHP

  • 772

I want to make it so that users of my app can search for images by color. I have an image table that saves the colors in hexadecimal as string type.

+----+-----------------+---------------+
| id | name            | color         |
+----+-----------------+---------------+
|  1 | Orange and Blue | 0c6cab        |
|  2 | Black and White | dba27e        |
|  3 | Full Colors     | 7ba709        |
|  4 | RoG             | 970404        |
|  5 | Triangles       | 167694        |
+----+-----------------+---------------+

The search must be done with colors similar to the one selected by the user, since if it is done with the exact color, it would be difficult to find many results. Is it possible to do this with hex or do I need to save the colors as another data type?

EDITION 1

By similar colors I mean that when the user searches for a red color, for example, the query returns all the colors with a red hue that are found in the table that do not exactly match the color searched for.

php

2 Answers

  1. Best Answer

    You would have to implement a distance algorithm. For example, let your color table be:

    SELECT 1 as id, 'Orange and Blue' as name,'0c6cab' as color UNION ALL
SELECT 2, 'Black and White','dba27e' UNION ALL
SELECT 3, 'Full Colors    ','7ba709' UNION ALL
SELECT 4, 'RoG            ','970404' UNION ALL
SELECT 5, 'Triangles      ','167694'

    (which is what you were showing)

    You can separate each hexadecimal number into its own field:

    SELECT 
    colors.*, 
    LEFT(color,2) as R , 
    RIGHT(LEFT(color,4),2) as G, 
    RIGHT(color,2) as B 
FROM colors;

    What I would give you:

    +----+-----------------+-------------------------------+
| id | name            | color         |   R | G  | B  |
+----+-----------------+-------------------------------+
|  1 | Orange and Blue | 0c6cab        |  0c | 6c | ab |
|  2 | Black and White | dba27e        |  db | a2 | 7e |
|  3 | Full Colors     | 7ba709        |  7b | a7 | 09 |
|  4 | RoG             | 970404        |  97 | 04 | 04 |
|  5 | Triangles       | 167694        |  16 | 76 | 94 |
+----+-----------------+-------------------------------+

    Now, you can express those columns with their transformation to decimal:

    SELECT 
    colors.*, 
    CONV(LEFT(color,2),16,10) as R , 
    CONV(RIGHT(LEFT(color,4),2),16,10) as G, 
    CONV(RIGHT(color,2),16,10) as B 
FROM colors;

+----+-----------------+--------------------------------+
| id | name            | color         |   R | G  | B   |
+----+-----------------+--------------------------------+
|  1 | Orange and Blue | 0c6cab        | 12  | 108 | 171|
|  2 | Black and White | dba27e        | 219 | 162 | 126|
|  3 | Full Colors     | 7ba709        | 123 | 167 |   9|
|  4 | RoG             | 970404        | 151 |   4 |   4|
|  5 | Triangles       | 167694        | 22  | 118 | 148|
+----+-----------------+--------------------------------+

    The "distance" from an RGB color (200,100,120) to each of your colors would be

    distancia = sqrt( (200-R)² + (100-G)² + (120-B)² )

    This would be better encapsulated in a function that takes two hexadecimals, splits them into RGB decimals, and does this calculation, returning the distance.

    Once the above is done, you only have to filter the query to those records whose distance from the entered color is less than your tolerance margin.

    • 4

  2. I think that the way you have it is fine and to make a query looking for similar colors you use the LIKE operator, for example

    Let's say the user at #77 and you in your query do this:

      select color from color where color LIKE '%#77%'

    the query would return this:

       +---------------+
   | color         |
   +---------------+
   | #777          |
   | #777777       |
   | #77797a       |
   | #778877       |
    • 0