动态构建SQL WHERE子句：避免冗余AND与正确处理初始WHERE关键字

SELECT * FROM orders WHERE (installation.active='1') AND (installation.InstallationStatus='0');

SELECT * FROM orders WHERE AND (installation.active='1') AND (installation.InstallationStatus='0');

// 过滤安装状态
if (isset($_SESSION['filter']['installStatus']) && !empty($_SESSION['filter']['installStatus'])) {
    $FilterInstallStatus ="AND (installation.InstallationStatus='".$_SESSION['filter']['installStatus']."')";
} else {
    $FilterInstallStatus = "";
}

// 过滤活动状态
if (isset($_SESSION['filter']['active']) && !empty($_SESSION['filter']['active'])) {
    $FilterActive ="AND (installation.active='".$_SESSION['filter']['active']."')";
} else {
    $FilterActive = "";
}

// 拼接所有过滤器到WHERE子句
$allrecords = $connection->query("(SELECT orders.*,installation.* FROM orders LEFT JOIN installation ON orders.OrderId = installation.OrderId WHERE".$FilterCreationDate." ".$FilterDateFull." ".$FilterModelName." ".$FilterInstallStatus." ".$FilterActive." ".$FilterUserFilter." ".$FilterLastUpdate." GROUP BY orders.OrderId) UNION (...) ORDER BY ...");

<?php
// 初始化一个空的过滤器条件字符串
$filter_query = '';

// 过滤安装状态
if (isset($_SESSION['filter']['installStatus']) && !empty($_SESSION['filter']['installStatus'])) {
    // 这是第一个条件，直接添加
    $filter_query = "(installation.InstallationStatus='".$_SESSION['filter']['installStatus']."')";
}

// 过滤活动状态
if (isset($_SESSION['filter']['active']) && !empty($_SESSION['filter']['active'])) {
    // 检查是否已有条件，如果存在，则先添加 ' AND '
    if ($filter_query != '') {
        $filter_query .= ' AND ';
    }
    // 添加当前条件
    $filter_query  .= "(installation.active='".$_SESSION['filter']['active']."')";
}

// 假设还有其他过滤器条件，也按照上述模式添加
// 例如：
/*
if (isset($_SESSION['filter']['someOtherCondition']) && !empty($_SESSION['filter']['someOtherCondition'])) {
    if ($filter_query != '') {
        $filter_query .= ' AND ';
    }
    $filter_query  .= "(some_table.some_column='".$_SESSION['filter']['someOtherCondition']."')";
}
*/

// 构建最终的SQL查询
// 只有当 $filter_query 不为空时，才添加 "WHERE " 前缀
$sql_where_clause = ($filter_query != '' ? "WHERE ".$filter_query : "");

$allrecords = $connection->query("
    (SELECT orders.*, installation.* 
     FROM orders 
     LEFT JOIN installation ON orders.OrderId = installation.OrderId 
     {$sql_where_clause} 
     GROUP BY orders.OrderId) 
    ORDER BY active DESC, CreationDate DESC, lastUpdate DESC, brandStatus DESC 
    LIMIT $start_from, $record_per_page
");

// 注意：原始问题中包含UNION查询，这里为简化示例，只展示了LEFT JOIN部分。
// 在实际应用中，UNION的两个部分都需要正确应用WHERE子句。
// 例如：
/*
$allrecords = $connection->query("
    (SELECT orders.*,installation.* 
     FROM orders LEFT JOIN installation ON orders.OrderId = installation.OrderId 
     {$sql_where_clause} 
     GROUP BY orders.OrderId) 
    UNION 
    (SELECT orders.*,installation.* 
     FROM orders RIGHT JOIN installation ON orders.OrderId = installation.OrderId 
     {$sql_where_clause} 
     GROUP BY orders.OrderId) 
    ORDER BY active DESC, CreationDate DESC, lastUpdate DESC, brandStatus DESC 
    LIMIT $start_from, $record_per_page
");
*/
?>