I have a set of arrays (year, season, genre) and I would like to run a query to select all records based on the values in the array at each iteration
for example:

for $i= 0 to length of query array

SELECT * from dbTableName where dbYearField = year & dbSeasonField = season & dbGenreField = genre

next $i

The goal is to only return records where all three fields from the arrays matched in each iteration. So if the arrays are

year(1929,1946,1973)

season(Spring,Spring,Fall)

genre(Landscape, Portrait,Portrait)

we would only get records where:

1929,Spring,Landscape is true
or
1946,Spring,Portrait is true
or
1973,Fall,Portrait is true.

I am new to both php and mysql and I can run basic queries where you are selecting based one set of values but I don't understand how to match the set of values at the different array positions and have them all in the same result. I'm sure I need some combination of php and mysql queries but I've been unable to find any other posts exactly on topic.

I may not have explained this well. I'll be glad to clarify if need be. I have used pseudo syntax to illustrate the problem. Thanks for your help.

    <?php

$year=array('1929','1946','1973');
$season=array('Spring','Spring','Fall');
$genre=array('Landscape', 'Portrait','Portrait');

foreach ($year as $k=>$v){

$q.= '(dbYearField = "'.$v.'" & dbSeasonField ="'.$season[$k].'"  & dbGenreField ="'.$genre[$k].'") OR';

}
$q =substr($q,0, -2);


$sql="SELECT * from dbTableName where $q";
echo $sql;

?>

    //result

    SELECT * from dbTableName where (dbYearField = "1929" & dbSeasonField ="Spring" & dbGenreField ="Landscape") OR(dbYearField = "1946" & dbSeasonField ="Spring" & dbGenreField ="Portrait") OR(dbYearField = "1973" & dbSeasonField ="Fall" & dbGenreField ="Portrait")

      Dagon, thanks so much. That was exactly what I needed. After studying the code I think I also understand how to code similar queries in the future. I appreciate your time.

        Write a Reply...