-3

I need your help. Here is an example:

Table A

id | colors
-------------------------
1  | green
2  | green, blue, red
3  | blue, red

Table B

id | colors | name
--------------------------
1  | green  | Apple
2  | blue   | Water
3  | red    | Fire
4  | yellow | Sun

In PHP I try to select distinct the names of table B, if the color is exist in table A. How can I do that?

Brad Ch
  • 11
  • 4

1 Answers1

1

You could use find_in_set and check for result > 0 

select distinct a.* 
from tableB b
inner join tableA a on  find_in_set(b.colors, a.colors) >0

and for obtain the result you could 

$sql="SELECT DISTINCT b.name. b.colors 
    FROM b INNER JOIN a ON find_in_set(b.colors, a.colors) >0";
$result=mysqli_query($con, $sql);
 while($rows=mysqli_fetch_array($result)) { 
    echo $rows["name"]; 
    echo $rows["colors"]; 
    }
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks for your answer. Works this version also in php? Because I need it there :S – Brad Ch Aug 15 '18 at 09:39
  • why using PHP when you can resolve the problem just using a query .. ??? this solution with a simple query avoid iteration for a find in array in PHP .. is more simple and more fast – ScaisEdge Aug 15 '18 at 09:42
  • You mean like: `$sql="SELECT DISTINCT name FROM b INNER JOIN a ON find_in_set(b.colors, a.colors) >0"; $result=mysqli_query($con, $sql); while($rows=mysqli_fetch_array($result)) { echo $rows["name"]; }` ? – Brad Ch Aug 15 '18 at 09:49
  • answer updated .. with colors too – ScaisEdge Aug 15 '18 at 09:53
  • I'v tried but still can't get any results. I don't need the colors, just the names. Is here maybe the problem? – Brad Ch Aug 15 '18 at 09:58
  • I had a false collation in the MySQL Database. Now I get a result but only the first and not all. Do you've again a tipp for me? – Brad Ch Aug 15 '18 at 11:14
  • `SELECT DISTINCT name FROM a, b WHERE CONCAT(",", a.colors, ",") REGEXP (b.colors)` was the solution, anyway thank you – Brad Ch Aug 15 '18 at 11:30