當我應用過濾器查詢不起作用時，如何知道Where子句是否已經存在（如果存在），那么接下來用AND替換where嗎？

查詢 1：AND (installation.InstallationStatus='0') 查詢 2：AND (installation.active='1')

當我創建一個過濾器并同時應用 Query1 和 Query 2 時，查詢會構建如下查詢： SELECT * FROM orders WHERE AND (installation.active='1') AND (installation.InstallationStatus='0')

但我想要這個查詢： SELECT * FROM orders WHERE (installation.active='1') AND (installation.InstallationStatus='0');

和 php 代碼在這里

            ```
            //Filter By installStatus
            if (isset($_SESSION['filter']['installStatus']) && !empty($_SESSION['filter']['installStatus'])) {
                $FilterInstallStatus ="AND (installation.InstallationStatus='".$_SESSION['filter']['installStatus']."')";
            } else {
                $FilterInstallStatus = "";
            }


            //Filter By Active
            if (isset($_SESSION['filter']['active']) && !empty($_SESSION['filter']['active'])) {
                $FilterActive ="AND (installation.active='".$_SESSION['filter']['active']."')";
            } else {
                $FilterActive = "";
            }

            $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 (SELECT orders.*,installation.* FROM orders RIGHT JOIN installation ON orders.OrderId = installation.OrderId WHERE".$FilterCreationDate." ".$FilterDateFull." ".$FilterModelName." ".$FilterInstallStatus." ".$FilterActive." ".$FilterUserFilter." ".$FilterLastUpdate." GROUP BY orders.OrderId) ORDER BY active DESC, CreationDate DESC, lastUpdate DESC, brandStatus DESC LIMIT $start_from, $record_per_page");

            ```

uj5u.com熱心網友回復：

您應該以不同的方式構建查詢。像這樣：

  $filter_query = '';

  //Filter By installStatus
        if (isset($_SESSION['filter']['installStatus']) && !empty($_SESSION['filter']['installStatus'])) {
            $filter_query = "(installation.InstallationStatus='".$_SESSION['filter']['installStatus']."')";
        }

        //Filter By Active
        if (isset($_SESSION['filter']['active']) && !empty($_SESSION['filter']['active'])) {
            if ($filter_query != '')
                $filter_query .= ' AND ';

            $filter_query  .= "(installation.active='".$_SESSION['filter']['active']."')";
        }

// here all other filters conditions with check if $filter_query is not empty
// and finally db query

$allrecords = $connection->query("(SELECT orders.*,installation.* FROM orders LEFT JOIN installation ON orders.OrderId = installation.OrderId ".($filter_query !='' ? "WHERE ".$filter_query : "")." GROUP BY orders.OrderId) ORDER BY active DESC, CreationDate DESC, lastUpdate DESC, brandStatus DESC LIMIT $start_from, $record_per_page");

uj5u.com熱心網友回復：

你可以把你的過濾器放在一個陣列中，然后加入它們AND：

$filter = array();

//Filter By installStatus
if (!empty($_SESSION['filter']['installStatus'])) {
    $filte[] = "(installation.InstallationStatus='".$_SESSION['filter']['installStatus']."')";
}

//Filter By Active
if ( !empty($_SESSION['filter']['active'])) {
    $filter[] = "(installation.active='".$_SESSION['filter']['active']."')";
}

// here all other filters conditions with check if $filter is not empty
// and finally db query
$where = !empty($filter) ? implode(' AND ', $filter) : '';

$allrecords = $connection->query("(SELECT orders.*,installation.* FROM orders LEFT JOIN installation ON orders.OrderId = installation.OrderId ".($filter_query !='' ? "WHERE ".$where : "")." GROUP BY orders.OrderId) ORDER BY active DESC, CreationDate DESC, lastUpdate DESC, brandStatus DESC LIMIT $start_from, $record_per_page");

1. 此方法允許您向查詢添加任意數量的過濾器，只需向$filter陣列添加一個新元素即可。
2. 無需usebothisset()并!empty()在相同的if條件下，!empty()就足夠了。

標籤：php sql mysqli

上一篇：如何將列的結果連接到新列

下一篇：使用case陳述句從兩個不同的表中選擇資料