Atif Atif - 3 months ago 14
MySQL Question

MySQL Select is not selecting based on given criteria

I am trying to run a SQL query but its not showing me the exact data.
In my search option, I have 4 options: search by start and end date, by website, by landing page, and by brand.

When I search by date and website, the query run fine, but when I search by landing page and brand, it's showing all values. I want the user to give an option to either search by website, landing page, or brand, or the user can search by all fields, but the date needs to be selected.

Here is my query I am running:

$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

try this

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

UPDATED:

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

UPDATE 2
below condition overwrite the $sql query string when brand not empty and all are empty . this one meet your expectation

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

        }