我可以在一个MySQL查询中使用多个WHERE语句吗？

Question

Disclaimer: I know it's possible to add AND and OR operators into a single WHERE statement, and this is generally the proper way to do it...

However, I'm building a complex query dynamically based on a variety of external factors. For the purposes of readability and and maintainability, it would be a lot tidier if I could have have one WHERE statement for certain conditions that always need to be met, but then add another (second) one later that will change depending on the circumstances.

A. Is this valid MySQL? B. Is it a terrible idea for some particular reason?

Here's a simplified example:

<?php 
$query = "
SELECT a,b,c
FROM table1
LEFT JOIN table2 on foo=bar
LEFT JOIN table3 on foo2=bar2
...
WHERE foobar = something_that_is_consistent 
AND boobar = something_else_consistent
...
";

if ( $something_special ) {
$query .= "WHERE ..."
}

if ( $something_else_special ) {
$query .= "WHERE ..."
}

This is a trivial example, but hopefully it demonstrates what I'm thinking about and how I'm trying to avoid big gangly nested conditionals inside the query string.

Answer 1

your WHERE should be one and then use AND

   <?php 
   $query = "
 SELECT a,b,c
 FROM table1
 LEFT JOIN table2 on foo=bar
 LEFT JOIN table3 on foo2=bar2
 ...
 WHERE foobar = something_that_is_consistent 
 AND boobar = something_else_consistent
 ...
 ";

if ( $something_special ) {
 $query .= "AND ..."
 }

 if ( $something_else_special ) {
 $query .= "AND ..."
 }