PHP & MYSQL : building a query based on multiple input values from users

Question

Hypothetically I three tables: * recipes - contains recipes * ingredients - list of items * meals - list of meals

I have a form that generates a selection like:

Choose what ingredients you have:
- apples
- bananas 
- cherries

Choose the meal this is for:
- breakfast
- lunch
- dinner

I want the user to be able to choose from either or none of the above, i.e they may choose apples OR cherries OR (bananas && lunch)

When I query MySQL my query is roughly

select recipes.* from recipes

or

select recipes.* from recipes, ingredients 
where recipes.id= ingredients.id and ingredients.list in ('apple');

or

select recipes.* 
from recipes, ingredients, meal 
where recipes.id= ingredients.id 
      and ingredients.list 
      and ingredients.id = meals.id 
      and ingredients.list ('apple') 
      and meals.list in ('lunch');

Is there a nice way of saying (in PHP) if this array exists (i.e. is_array(ingredients) add to the query the table (ingredients) and at the end tack on (".ingredients.list in ('apple'))...

without having to write all the possible combinations or possible inputs (i.e. the user's selected from the ingredients list, or from the ingredients and meals list, or from no lists)?

Answer 1

There are several approaches to tackle this.

If you want to know if a key exists in an array you can use array_key_exists

Answer 2

You would create two arrays for possible tables and where chunks, then make the query that gathers everything:

$wheres=array();
$tables=array();
if(isset($_POST['ingredients'])) {
  $tables[]='ingredients';

  $ingredients=array_map('mysqL_real_escape_string', $_POST['ingredients']);
  $wheres[]='ingredients.list IN (\''. join(', ', $ingredients). '\')';
  //add other wheres if you want
}
if(isset($_POST['meals'])) {
  $tables[]='meal';

  $meals=array_map('mysqL_real_escape_string', $_POST['meals']);
  $wheres[]='meals.list IN (\''. join(', ', $ingredients). '\')';
  //add other wheres if you want
}

if(!$tables) {
  echo 'You have not chose anything!';
} else {
  $query = 'SELECT * FROM '. join(',', $tables). ' WHERE '. join(' AND ', $wheres);
  //do other stuff..
}

Answer 3

I see no "all the possible combinations or possible inputs" here but just all possible inputs.
I'd check all possible inputs and add them to the query if filled.