MySql Query with many where clause combinations

Question

I have to make option to download report in excel where user will enter "from date", "to date" and "market name". Based on that data will be fetched from the database. All the 3 fields are not mandatory, so if user will enter any value then it will fetch data based on the value otherwise it will fetch all the data. Just for 3 fields I have to write 8 combinations of where clause, so my question is that if there are more input value from user end, then I have to write many combinations of where clause, sometimes even more than 20 combinations. Is there any easy and efficient way to do this.

<?php

    if($date_from != NULL && $date_to != NULL  && $market != NULL){

      $whereCondition = "WHERE Date >= '$date_from' AND Date <= '$date_to' AND marketId = '$market' ";

    }elseif($date_from == NULL && $date_to == NULL && $market == NULL){

       $whereCondition = "WHERE 1";

    }elseif($date_from != NULL && $date_to == NULL  && $market != NULL){

        $whereCondition = "WHERE Date >= '$date_from' AND marketId = '$market' ";

    }elseif($date_from != NULL && $date_to == NULL  && $market == NULL){

         $whereCondition = "WHERE Date >= '$date_from' ";

    }elseif($date_from == NULL && $date_to != NULL  && $market != NULL){

        $whereCondition = "WHERE Date <= '$date_to' AND marketId = '$market' ";

    }elseif($date_from == NULL && $date_to != NULL  && $market == NULL){

        $whereCondition = "WHERE Date <= '$date_to'";

    }elseif($date_from == NULL && $date_to == NULL  && $market != NULL){

        $whereCondition = "WHERE marketId = '$market' ";

    }elseif($date_from != NULL && $date_to != NULL  && $market == NULL){

        $whereCondition = "WHERE Date >= '$date_from' AND Date <= '$date_to' ";
    }  


//finally my query will be below:
    //MYSQL QUERY
    $sql_data = "SELECT * FROM table_name $whereCondition ";

Push conditions to array and implode them with 'AND ' separator — turson, Jul 08 '14 at 08:06
or use **(Date >= '$date_from' OR $date_from IS NULL) AND ...** — StanislavL, Jul 08 '14 at 08:09
if $date_from is empty then condition will be **(Date >= '' OR $date_from IS NULL) AND ...** which is wrong. — Dinesh Kumar Talaicha, Jul 08 '14 at 08:18
@DineshKumarTalaicha All the various combinations of possibilities of NULL can be handled in a single WHERE statement. Please see my answer below. — Joseph B, Jul 08 '14 at 08:38
Build it up in pieces. Was `marketId` provided? Then add that condition to the query. Was `date_from` provided? Add that too. Was... do them one at a time. Note that you appear potentially open to SQL Injection, please use [parameterized queries](http://stackoverflow.com/q/60174/812837). Also, the proper way to query positive contiguous-range types (like date/time/timestamps) is with [an exclusive upper bound](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) (`<`). — Clockwork-Muse, Jul 08 '14 at 08:44

Joseph B · Answer 1 · 2014-07-08T08:37:08.557

0

The WHERE clause can be expressed using the COALESCE function to provide an alternate value if the original value is NULL, as below:

 $whereCondition = "WHERE Date >= COALESCE('$date_from', Date) AND Date <= COALESCE('$date_to', Date) AND marketId = COALESCE('$market', marketId)";

Thus, for example, if '$date_from' is NULL, then it would take the value of the Date column of the current row, making the condition Date >= Date return TRUE.

Reference:

COALESCE function on MySQL Reference Manual

edited Jul 08 '14 at 08:37

answered Jul 08 '14 at 08:28

Joseph B

5,519
1
15
19

Except, I rather doubt this will be able to use indices for the `WHERE`, so is likely to be slow on large tables. – Clockwork-Muse Jul 08 '14 at 08:39
Thanks a lot Joseph, this concept is working for me. – Dinesh Kumar Talaicha Jul 09 '14 at 06:49

score 0 · Answer 2 · answered Jul 08 '14 at 09:50

Building up an array and imploding the condition (if required):-

<?php

    $where_array = array();

    if($date_from != NULL)
    {
        $where_array[] = " Date >= '$date_from' ";
    }
    if($date_to != NULL)
    {
        $where_array[] = " Date <= '$date_to' ";
    }
    if($market != NULL)
    {
        $where_array[] = " marketId = '$market' ";
    }

    if (count($where_array) > 0)
    {
        $whereCondition = ' WHERE '.implode(' AND ', $where_array);
    }
    else
    {
        $whereCondition = '';
    }

//finally my query will be below:
    //MYSQL QUERY
    $sql_data = "SELECT * FROM table_name $whereCondition ";

MySql Query with many where clause combinations

2 Answers2