MySQL Select 未根据给定标准进行选择

Question

我正在尝试运行 SQL 查询，但它没有向我显示确切的数据。在我的搜索选项中，我有 4 个选项:按开始和结束日期、按网站、按着陆页和按品牌搜索。

当我按日期和网站搜索时，查询运行良好，但当我按着陆页和品牌搜索时，它显示所有值。我希望用户可以选择按网站、着陆页或品牌进行搜索，或者用户可以按所有字段进行搜索，但需要选择日期。

这是我正在运行的查询:

$sql = "
    SELECT brand, cpa, sum(ftds), website, landingpage, ftddate
    FROM ftd
    WHERE ftddate BETWEEN CAST('$startdate' AS DATE)
    AND CAST('$enddate' AS DATE)
    AND (website='$website')
    OR (landingpage='$landingpage') 
    group by brand
";

Answer 1

试试这个

$sql ="SELECT `brand`, `cpa`, sum(`ftds`),`website`,`landingpage`,`ftddate` FROM ftd WHERE `ftddate` BETWEEN CAST('$startdate' AS DATE) AND CAST('$enddate' AS DATE) AND ((`website`='$website') OR (`landingpage`='$landingpage') OR (`brand`='$brand')) group by `brand`"; 

更新:

        $concatenate='';

        $group_by_string ='  group by `brand`';
        ^^^^^^^^^^^^^^^^

        if(!empty($website))
        {
           $concatenate.=" AND `website`='$website'  ";

        }
        if(!empty($landingpage))
        {
           $concatenate.=" AND `landingpage`='$landingpage'  ";
        }
        if(!empty($brand))
        {
            $concatenate.=" AND `brand`='$brand'  ";
            $group_by_string ='  group by `landingpage`';
            ^^^^^^^^^^^^^^^^                

        }

        $sql ="SELECT `brand`, `cpa`, sum(`ftds`),`website`,`landingpage`,`ftddate` 
        FROM ftd 
        WHERE `ftddate` 
        BETWEEN CAST('$startdate' AS DATE) 
        AND CAST('$enddate' AS DATE) 
        $concatenate  $group_by_string ";
        ^^^^^^^^^^^

更新2
当品牌不为空且全部为空时，以下条件将覆盖 $sql 查询字符串。这个符合你的期望

        if(!empty($brand) && empty($website) && empty($landingpage))
       {
       $sql ="SELECT `brand`, `cpa`, `ftds`,`website`,`landingpage`,`ftddate` 
        FROM ftd 
        WHERE `ftddate` 
        BETWEEN CAST('$startdate' AS DATE) 
        AND CAST('$enddate' AS DATE) 
        $concatenate  $group_by_string";

        }