Query 1: AND (installation.InstallationStatus='0') Query 2: AND (installation.active='1')

When I create a filter and apply both Query1 and Query 2 so query build something like this Query: SELECT * FROM orders WHERE AND (installation.active='1') AND (installation.InstallationStatus='0')

But I Want this Query: SELECT * FROM orders WHERE (installation.active='1') AND (installation.InstallationStatus='0');

and the php code is here

            ```
            //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");

            ```

CodePudding user response：

You should build query differently. Like this:

  $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");

CodePudding user response：

You can put your filters in an array and later join them with 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. This method allow you add any number of filters to your query, just add a new element to $filter array.
2. No need to useboth isset() and !empty() in the same if condition, !empty() is enough.