MySql查询有很多where子句组合 - MySql Query with many where clause combinations

MySql Query with many where clause combinations

本文关键字：where 子句组合查询 MySql | 更新日期: 2023-09-27

我必须在excel中选择下载报告，用户将输入"从日期"，"到日期"answers"市场名称"。在此基础上，将从数据库中获取数据。所有3个字段都不是强制性的，所以如果用户将输入任何值，那么它将根据该值获取数据，否则它将获取所有数据。对于3个字段，我必须编写8个where子句的组合，所以我的问题是，如果用户端的输入值更多，那么我必须编写许多where子句的组合，有时甚至超过20个组合。有什么简单有效的方法来做这件事吗?

<?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 ";

如果原始值为NULL，则可以使用COALESCE函数来表示WHERE子句，如下所示:

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

因此，例如，如果'$date_from'为NULL，则它将取当前行的Date列的值，使条件Date >= Date返回TRUE。

参考

参考手册

构建数组并内嵌条件(如果需要):-

<?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 ";