CODEWITHSUNDEEP
phpmysql
BernaMariano
BernaMariano

Reputation: 866

Building a query with options

I need to make a query in MySQL. But the query needs to be made dinamically with options choosen by the user.

The options are Day, Month, Year & SellerID.

A user will choose if he want to view the sales history from sellers by, days, months and years.

But, let's say he wants to see all sales on March, 2012. He will leave Day in blank and receive all sales from March 01 till March 31...

And maybe he leaves the option Month in blank, and set Day=15 and Year=2012, he will get the sales history from the whole year 2012, and the 15th day of each month only.

My program needs to build a query that covers exactly what he wanted to view.

The question is:
Do I really need to use thousands of IF on my code? Isn't there a better way to do that?

if( ($day==0) && ($month==0) && ($year>0) ){

    $query.="`date`>='{$year}-01-01' AND `date`<='{$year}-12-31'";

}elseif( ($day==0) && ($month>0) && ($year>0) ){

    $query.="`date`>='{$year}-{$month}-01' AND `date`<='{$year}-{$month}-31'";

}elseif{

etc...

Upvotes: 1

Views: 78

Answers (2)

Vardan Gupta
Vardan Gupta

Reputation: 3585

RESTRICT SORTING ON YEAR, MONTH, DATE format or MONTH, Year Month format or Year Format, means level your sorting in hierarchy and for that You need to handle 3 cases here which would work simply on two WHERE clauses DATE>=DYNAMIC_START_DATE AND DATE<=DYNAMIC_END_DATE (You can also use BETWEEN here)

  • If only Year is given then you should set your start date as 1st jan year and end date as 31st dec year
  • If month AND Year is given then you should set your start date as 1st month year and end date as [2830/31] month year
  • If only date and month AND Year is given then you should set your start date as date month year and end date as date month year.

Upvotes: 0

rid
rid

Reputation: 63442

You could preprocess the values before using them:

$year  = $year  > 0 ? (int) $year  : 2012;
$month = $month > 0 ? (int) $month : 1;
$day   = $day   > 0 ? (int) $day   : 1;

$query .= sprintf("`date` BETWEEN '%4d-%2d-%2d' AND '%4d-%2d-%2d'",
    $year, $month, $day,
    $year, $month, 31
);

Upvotes: 3

Related Questions