PHP 与MYSQL：根据用户的多个输入值构建查询

发布于 2024-09-18 01:05:58 字数 949 浏览 6 评论 0原文

假设我有三个表： * 食谱 - 包含食谱 * 成分 - 物品清单 * 餐食 - 餐食列表

我有一个生成选择的表单，例如：

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

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

我希望用户能够从以上任一选项中进行选择，或者都不进行选择，即他们可以选择苹果或樱桃或（香蕉和午餐

）我查询 MySQL 我的查询大致是

select recipes.* from recipes

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

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');

是否有一个很好的方式来表示（在 PHP 中）该数组是否存在（即 is_array(ingredients) 将表（成分）添加到查询中，并在最后添加 (".ingredients.列表在（'苹果'））...

而不必编写所有可能的组合或可能的输入（即用户从成分列表中选择，或从成分和膳食列表中选择，或从无列表中选择）？

原文

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

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

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)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

赠佳期 2024-09-25 01:05:58

有几种方法可以解决这个问题。

如果你想知道数组中是否存在某个键，可以使用 array_key_exists

回复收藏 0 原文

<逆流佳人身旁 2024-09-25 01:05:58

您将为可能的表和块创建两个数组，然后进行收集所有内容的查询：

$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..
}

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..
}

回复收藏 0 原文