Use an Array to Cycle Through a MySQL Where Statement

I'm trying to create a loop that will output multiple where statements for a MySQL query. Ultimately, my goal is to end up with these four separate Where statements:

`fruit` = '1' AND `vegetables` = '1'
`fruit` = '1' AND `vegetables` = '2'
`fruit` = '2' AND `vegetables` = '1'
`fruit` = '2' AND `vegetables` = '2'

My theoretical code is pasted below:

<?php

$columnnames = array('fruit','vegetables');
$column1 = array('1','2');
$column2 = array('1','2');

$where = '';
$column1inc =0;
$column2inc =0;

while( $column1inc <= count($column1) ) {
    if( !empty( $where ) )
    $where .= ' AND ';
    $where = "`".$columnnames[0]."` = ";
    $where .= "'".$column1[$column1inc]."'";

    while( $column2inc <= count($column2) ) {
        if( !empty( $where ) )
        $where .= ' AND ';
        $where .= "`".$columnnames[1]."` = ";
        $where .= "'".$column2[$column2inc]."'";

            echo $where."\n";

    $column2inc++;
}

$column1inc++;
}

?>

When I run this code, I get the following output:

`fruit` = '1' AND `vegetables` = '1'
`fruit` = '1' AND `vegetables` = '1' AND `vegetables` = '2'
`fruit` = '1' AND `vegetables` = '1' AND `vegetables` = '2' AND `vegetables` = ''

Does anyone see what I am doing incorrectly? Thanks.

From stackoverflow

• You never reset $where.

  BTW. Don't do

  if( !empty( $where ) )
  $where .= ' AND ';
  $where = "`".$columnnames[0]."` = ";
  

  as this is dangerously ambiguous. Do

  if( !empty( $where ) ) $where .= ' AND ';
  $where = "`".$columnnames[0]."` = ";
  

  or

  if( !empty( $where ) ) {
    $where .= ' AND ';
  }
  $where = "`".$columnnames[0]."` = ";
  

  Ryan : Gotcha, thanks!

• I'm suggesting the following code:

  $columnnames = array('fruit','vegetables');
  $column1 = array('1','2');
  $column2 = array('1','2');
  
  $list = array();
  for($i = 0; $i < count($column1); $i++) {
          for($k = 0; $k < count($column2); $k++) {
                  $str = sprintf("`%s` = `%d` AND `%s` = `%d`",
                          $columnnames[0], 
                          $column1[$i], 
                          $columnnames[1], 
                          $column2[$k]
                  );
                  $list[] = $str;
          }
  }
  
  echo implode(' AND ', $list);
  

  Cheers,
  Fabian

  Ryan : This code is interesting. While it's combing all 4 statements into one, it looks like it's doing pretty much what I want. I'll manipulate a bit and see what happens. Thanks for cleaning up my code, too.