mysql - Match Where Multiple Columns are... PHP PDO -

i have search query works, not way had hope. here:

 try{     /*      create search query.       */      //include database config file.     include('config.php');     //get values angular.     $valuespost=$_post;     $values=array();     foreach ($valuespost $rows) {         $decodedvalues=json_decode($rows, true);         $values[]=$decodedvalues;     }     //get table post values.     $table=$values[0]["table"];     //get limit post values.     $limit=$values[0]["limit"];     //get offset post values.     $offset=$values[0]["offset"];     //get orderby post values.     $orderby=$values[0]["orderby"];     //unset whole array     unset($values[0]);      //create cats fields         $cats=array_keys($values[1]);         if(empty($cats)) {             $cats = null;         } else {             foreach($cats &$val){                 $val="cat_id = :".$val;             }             $cats=implode(" or ", $cats);         }     //create subcats fields         $subcats=array_keys($values[2]);         if(empty($values[2])) {             $subcats[0] = null;         } else {             foreach($subcats &$val){                 $val="sub_cat_id = :".$val;             }             $subcats=implode(" or ", $subcats);         }     //create colourcats fields         $colourcats=array_keys($values[3]);         if(empty($colourcats)) {             $colourcats[0] = null;         } else {             foreach($colourcats &$val){                 $val="colour_id = :".$val;             }             $colourcats=implode(" or ", $colourcats);         }     $where = "";     //create statement         if(empty($cats[0]) && empty($subcats[0])){             $where = $colourcats;         }         if(empty($cats[0]) && empty($colourcats[0])){             $where = $subcats;         }         if(empty($subcats[0]) && empty($colourcats[0])){             $where = $cats;         }         if(empty($colourcats[0]) && !empty($cats[0]) && !empty($subcats[0])){             $where = $cats." , ".$subcats;         }         if(empty($subcats[0]) && !empty($cats[0]) && !empty($colourcats[0])){             $where = $cats." , ".$colourcats;         }         if(empty($cats[0]) && !empty($subcats[0]) && !empty($colourcats[0])){             $where = $subcats." , ".$colourcats;         }         if(!empty($cats[0]) && !empty($subcats[0]) && !empty($colourcats[0])){             $where = $cats." , ".$subcats." , ".$colourcats;         }     //search query.     $search="select * $table $where order $orderby limit $limit offset $offset";       /*       database connection.     */      //crate database connection variable: $conn , error checking attributes.         $conn = new pdo($db_settings, $db_user, $db_pass);     $conn->setattribute( pdo::attr_errmode, pdo::errmode_exception );     /*      pdo work.      */      //search_like_all query!     //prepare query.     $search_qry=$conn->prepare($search);     //for each array index create array $rows.     foreach ($values $rows) {         //bind each value $value_fields $rows array.         foreach ($rows $key => &$value) {             switch(gettype($value)) {                 case 'integer':                 case 'double':                 $search_qry->bindparam(':' . $key, $value, pdo::param_int);                 break;                 default:                 $search_qry->bindparam(':' . $key, $value, pdo::param_str);             }         }        }     $search_qry->execute();     $rows = $search_qry->fetchall(pdo::fetch_assoc);      echo json_encode($rows); } catch(pdoexception $e) {     echo 'error: ' . $e->getmessage(); } 

it builds query fine. in angularjs script build multidimensional array index [0] contains data such table, orderby , limits. index [1] contains array of categories. index[2] array of sub categories , index[3] array of colour categories. problem search query bring data not expecting. lets send type of array:

values[0]={table: table, order_by: prod_code, limit: 10, offset 0}  values[1]={cat_id0: 1};  values[2]={sub_cat_id0: 1, sub_cat_id1: 3}  values[3]={colour_id0: 1, colour_id1: 2} 

i want products in cat 1 sub cats 2 & 3 , colour id 1 & 2. changing order when remove sub cat id array still in search, assume product later being picked colour id. query wrong or way in searching.

from array above:

$search="select * table cat_id=:cat_id0 , sub_cat_id=:sub_cat_id0 or sub_cat_id=:sub_cat_id1 , colour_id=:colour_id0 or colour_id=:colour_id1 order prod_code limit 10 offset 0" 

i think big problem have order of precedence between and , or.

 select       , b       , c       , or b , c       , (a or b) , c    ( select 1 union select 0 )    cross    join ( select 1 b union select 0 ) b    cross    join ( select 1 c union select 0 ) c    not (a.a = b.b , a.a = c.c)   order c, b,              b       c  or b , c  (a or b) , c   ------  ------  ------  ------------  --------------      1       0       0             1               0      0       1       0             0               0      1       1       0             1               0      0       0       1             0               0      1       0       1             1               1      0       1       1             1               1   

that say

 foo = or foo = b , bar = c 

is evaluated as:

 ( foo = ) or ( foo = b , bar = c ) 

when want is:

 (foo = or foo = b ) , ( bar = c ) 

---

i'm having trouble deciphering code doing.

for example, why need permutations of conditions in building where clause? why checks? why not simpler, like:

$where = " 1=1"; if (!empty($cats[0]) {    $where .= " , (" . $cats . ")"; }  if (!empty($subcats[0]) {    $where .= " , (" . $subcats . ")"; }  if (!empty($colourcats[0]) {    $where .= " , (" . $colourcats . ")"; }  

also, or conditions testing equality

   foo = or foo = b or foo = c or foo = d 

can expressed more elegantly using in

   foo in (a,b,c,d) 

---

//create cats fields     $cats=array_keys($values[1]);     if(empty($cats)) {         $cats = null;     } else {          $cats=" cat_id in (". implode(",",$cats) . ");      }