Finding matches in multiple columns of mysql with an array set of data in PHP

Question

I have a PHP array: Array(keyword1,blah0,blah1,blah2) and an SQL query.

In the query I want to find if one of the data in Array exists in one of the few columns selected (columnA|columnB|columnC) and fetch that row if true.

32bitfloat · Accepted Answer · 2012-07-12 15:57:33Z

1

You'll need a query like this:

$instring = implode("','", $array);
$sql = "SELECT `xy` FROM `z` WHERE (`columnA` IN ('".$instring."')
OR `columnB` IN ('".$instring."')
OR `columnC` IN ('".$instring."'))";

answered Jul 12, 2012 at 15:57

32bitfloat

7712 gold badges8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Besnik · Accepted Answer · 2012-07-13 19:30:50Z

1

I think this is what you want. I couldn't tell if you wanted to check every column for every value in your array.

SELECT * FROM tableName
WHERE columnA = $phpArray[0] OR
columnA = $phpArray[0] OR
columnB = $phpArray[0] OR
columnC = $phpArray[0] OR
columnA = $phpArray[1] OR
columnB = $phpArray[1] OR
columnC = $phpArray[1] OR
columnA = $phpArray[2] OR
columnB = $phpArray[2] OR
columnC = $phpArray[2] OR 
... 
columnC = $phpArray[n];

You can use PHP to create this statement in a loop

$sql = "SELECT * FROM tableName WHERE ";
for($i=0;$i<count(phpArray)-1;$i++){
   $sql .= "columnA = "+"'"+$phpArray[$i]+"'"+ OR "
   $sql .= "columnB = +"'"+$phpArray[$i]+"'"+ OR "
   $sql .= "columnC = +"'"+$phpArray[$i]+"'"+ OR "
}
$sql .= "columnA = +"'"+$phpArray[$i]+"'"+ OR "
$sql .= "columnB = +"'"+$phpArray[$i]+"'"+ OR "
$sql .= "columnC = +"'"+$phpArray[$i]+"'"+;"
*send sql query

edit: Fixed errors in code

edited Jul 13, 2012 at 19:30

Besnik

6,5581 gold badge33 silver badges34 bronze badges

answered Jul 12, 2012 at 15:53

StoicJester

3642 silver badges12 bronze badges

2 Comments

octern Over a year ago

+1 because this will work, but 32bitfloat's answer using SQL's IN is more readable.

StoicJester Over a year ago

Thanks, it looks like I made a couple of mistakes anyway. Didn't know about IN statements, so I learned something out of this. :)

Collectives™ on Stack Overflow

Finding matches in multiple columns of mysql with an array set of data in PHP

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related