CODEWITHSUNDEEP
phpmysqlarrays
Taniel
Taniel

Reputation: 27

PHP array match MYSQL string

I have a color array and want to match the color row in MYSQL,

as long as the color row match one of the colors in array, then take it out.

the following $colorArray should match id 2 & 3, because those rows contain yellow & blue

$colorArray = array('yellow','blue');

+------+---------+------------+
| id   | product | color      |
+------+---------+------------+
|    1 | tShirt  | red,green  |
|    2 | jeans   | yellow,red |
|    3 | shorts  | black,blue |
+------+---------+------------+

I just want a MySQL SELECT syntax, I know it can be done with PHP array_intersect to do some filters, but I don't want to for some reasons.

So is it possible?

Upvotes: 1

Views: 265

Answers (6)

Bhavin Bhandari
Bhavin Bhandari

Reputation: 7

$colorArray = array('yellow','blue');
$colors = implode(",",$colorArray);  
$query = "SELECT color FROM TableName WHERE color = '.$colors.'";

Upvotes: 0

genespos
genespos

Reputation: 3311

You can try something like:

$colorArray = array('yellow','blue');
$colors = join(',',$colorArray);  
$sql = "SELECT '$colors' AS colors,
       color,
       CONCAT('(', REPLACE(color, ',', '(\\,|$)|'), '(\\,|$))') AS regex,
       '$colors' REGEXP CONCAT('(', REPLACE(color, ',', '(\\,|$)|'), '(\\,|$))') AS intersect
       FROM YourTableName
       GROUP BY id HAVING intersect > 0";

Upvotes: 0

Vigneswaran S
Vigneswaran S

Reputation: 2094

Use FIND_IN_SET() in your query .Try this let me know. I hope I sloved your problem your required query 

SELECT * FROM t3 WHERE FIND_IN_SET('yellow', colour) or FIND_IN_SET('blue', colour)

code:

<?php
$colorArray = array('yellow','blue','red');
$where1 ="FIND_IN_SET('$colorArray[0]', colour) ";
$where="";
for($i=1;$i<sizeof($colorArray);$i++){
$color=$colorArray[$i];
$where .= "  or FIND_IN_SET('$color', colour) ";
}
$sql="SELECT * FROM t3 WHERE $where1 $where";
//echo $sql;//SELECT * FROM t3 WHERE FIND_IN_SET('yellow', colour) or FIND_IN_SET('blue', colour) or FIND_IN_SET('red', colour) 
// try running this query 
?>

Upvotes: 0

shozdeh
shozdeh

Reputation: 172

It is easy:

<?php
$color = array('yellow', 'red');
$query = "";
foreach($color as $c)
   $query .= "color like '%".$c."%' or ";
$sql = "SELECT * FROM <your_table_name> WHERE ".$query." 1=0";
?>

Upvotes: 1

Adel Bachene
Adel Bachene

Reputation: 994

I think you are having a problem in combining an in and a Like %S%, so A REGEXP might be more efficient, but you'd have to benchmark it to be sure, e.g.

$colorsStr = implode('|', $colorArray); // => 'yellow|blue'
$sql = "SELECT * FROM <your_table_name> WHERE REGEXP '" . $colorsStr . "'";

Upvotes: 1

Volodymyr Chumak
Volodymyr Chumak

Reputation: 766

Try this code:

$colorsStr = implode(',', $colorArray); // => 'yellow,blue'
$sql = 'SELECT id FROM <your_table_name> WHERE color IN('.$colorsStr.')';

Upvotes: 0

Related Questions