php - 我的SQL语句有什么问题？

我正在创建一个站点，用户可以在其中选择一个出租属性，该属性具有两个可以从中选择的字段：家具和宠物。两个选择框中的选项是“是”和“否”。

<select name="furnished">
  <option value="">
  <option value="yes">Yes</option>
  <option value="no">No</option>
</select>

<select name="pets">
  <option value="">
  <option value="yes">Yes</option>
  <option value="no">No</option>
</select>

我正在根据用户在这些字段中选择的内容编写一条SQL语句。

$sql = 'SELECT *
       FROM properties
       WHERE num_bedrooms >= ' . $_GET['num_bedrooms'] .
       ' AND num_bathrooms >= ' . $_GET['num_bathrooms'];

if($_GET['furnished'] == 'yes') { //if the furnished is set to yes
  $sql .= ' AND furnished = "yes" OR furnished = "partially"';
} else if($_GET['furnished'] == 'no') { //if the furnished is set to no
  $sql .= ' AND furnished = "no" OR furnished = "description"';
}

if($_GET['pets'] == 'yes') { //if the pets is set to yes
  $sql .= ' AND pets = "yes" OR pets = "cats" OR pets = "dogs"';
} else if($_GET['pets'] == 'no') { //if the pets is set to no
  $sql .= ' AND pets = "no" OR pets = "description"';
}

如果用户为家具选择“是”，我希望它显示所有家具（是）或部分（部分）装饰的属性。如果用户选择“否”，我希望它显示所有未提供的属性（否）或具有特殊描述（描述）的属性。

如果用户为宠物选择是，我希望它显示所有允许所有宠物的属性（是）或仅允许猫（猫）或仅允许狗（狗）的属性。如果用户选择“否”，我希望它显示所有不允许携带宠物的属性（否）或具有特殊描述（说明）的属性。

例如，如果用户在Furnished上选择Yes，在Pets上选择Yes，这是输出的SQL语句：

SELECT * FROM properties
WHERE num_bedrooms >= 1
AND num_bathrooms >= 1
AND furnished = "yes" OR furnished = "partially"
AND pets = "yes" OR pets = "cats" OR pets = "dogs"

问题在于，它当前将返回满足where子句中带家具或宠物要求的结果的结果，而我需要它返回满足where子句中带家具和宠物要求的结果的结果。

因此，它当前将返回一个结果，显示Furnished设置为yes，而pets设置为no。
如何将家具设置为是且宠物设置为是，返回所有结果？

我的SQL语句有什么问题？

最佳答案

AND在运算符优先级之前在OR之前，因此您的查询内容类似于

SELECT * FROM properties
WHERE
(num_bedrooms >= 1 AND num_bathrooms >= 1 AND furnished = "yes")
OR (furnished = "partially" AND pets = "yes")
OR pets = "cats"
OR pets = "dogs"

这与您的预期不符。尝试

SELECT * FROM properties
WHERE num_bedrooms >= 1
AND num_bathrooms >= 1
AND (furnished = "yes" OR furnished = "partially")
AND (pets = "yes" OR pets = "cats" OR pets = "dogs")

甚至更好

SELECT * FROM properties
WHERE num_bedrooms >= 1
AND num_bathrooms >= 1
AND furnished IN ("yes", "partially")
AND pets IN ("yes", "cats", "dogs")

您不应该尝试SELECT *命名所需的列！

关于php - 我的SQL语句有什么问题？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7937595/