Create PHP color search

Question

Create PHP color search

Navigation

#1 by (3 votes)
#2 by (0 votes)

2

I want to make the users of my application able to search for images by color. I have a table of images that stores the colors in hexadecimal as a 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 should be done with colors similar to the one selected by the user, because if it is done with the exact color, it would be difficult to find many results. Is it possible to do this with hexadecimal or should I save the colors as another type of data?

EDITION 1

By similar colors, I mean that when the user searches for a red color, the query returns all the colors of red tonality that are in the table without exactly matching the color sought.

mysql php

asked by Edwin V 08.03.2017 в 21:12

source

2 answers

0

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

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

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

the query will devour you this:

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

answered by 08.03.2017 в 21:28

Load different texts in the same TextView (SharedPreferences) ALGORITHM PARTITION QS

score 3 · Accepted Answer

You should implement a distance algorithm. For example, be your color table:

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 in 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" of a 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, separates them in decimal RGB and makes this calculation, returning the distance.

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