在php中有条件地管理mysql查询

Question

我有一个查询，在这里我得到了学生人数。现在，我想修改该查询并有条件地对其进行管理。这是我的查询

SELECT count(students.id) AS student_count,
FROM students
LEFT JOIN students_admission ON students_admission.student_id = students.id
WHERE student_gender='Male'
AND students.address = 'Address'
GROUP BY id

这就是我想要的

$admission_date = null;
SELECT count(students.id) AS student_count,
FROM students
LEFT JOIN students_admission ON students_admission.student_id = students.id
WHERE student_gender='Male'
AND students.address = 'Address'
AND students_admission.admission_date = '$admission_date'
GROUP BY id

但是，我想补充一点AND students_admission.admission_date = 'admission_date'仅当$admission_date不为null时才出现此条件。所以，我试过这个

SELECT count(students.id) AS student_count,
FROM students
LEFT JOIN students_admission ON students_admission.student_id = students.id
WHERE student_gender='Male'
AND students.address = 'Address'
CASE WHEN '$admission_date' IS NOT NULL THEN students_admission.admission_date = '$admission_date' END
GROUP BY id

其返回语法错误。我该如何处理？

Answer 1

只需使用布尔逻辑：

AND (
:admission_date IS NULL 
OR students_admission.admission_date = :admission_date
)

您也可以将其表述为：

students_admission.admission_date = coalesce(:admission_date, students_admission.admission_date)

请注意，您应该使用参数化查询，而不是将字符串变量混合到查询字符串中：这样既高效又安全(您当前的代码广泛暴露在SQL注入中(。请参阅这篇著名的SO帖子，了解原因和方法。

此外，请注意，在查询的where子句中的left join-ed表上设置条件实际上会将left join变成inner join。您可能希望在join的on子句中使用此条件。