选择具有多个更高和更低值的Mysql

Question

i am in need of a search query based on 6 values from 3 columns day, month, year, right now i have got it some how working only for month and year but i can not get the day to perform correctly.

for example if some one wants to search from day, month year to day month year. my current query

    //From
    list($fy,$fm,$fd)   = explode ('-', 2013-2-20);
    //to
    list($ty,$tm,$td)   = explode ('-', 2014-9-1);

    $add = " AND 
            ( month >= '".$fm."'
            AND year >='".$fy."') AND ( 
            month <= '".$tm."' 
            AND year <=   '".$ty."'
            ) ";

as you can see the day is not included, your time is much appreciated.

Answer 1

Going on with what you have. You would just add day in the same way you added month and year:

...
$add = " AND 
        ( month >= '".$fm."'
        AND year >='".$fy."'
        AND day >='".$fd."') AND ( 
        month <= '".$tm."' 
        AND year <=   '".$ty."'
        AND day <=   '".$td."'
        ) ";
...

Since all the items are independent of each other, this works of the literal values are higher/lower in each item. Which is probably not what you want. You will need to convert the strings '2013-2-20' into DATE and then use those. This can be done with str_to_date(). So for example str_to_date('2013-2-20','%Y-%m-%d').

Notice: I'm aware that you cannot change the database but for future references it's a good idea to have a single DATE datatype for your database. It's not good design to separate them into separates things such as day, month, and year. As with using the DATE datatype it can easily find lower and higher dates by simply comparing the dates (along with date functions using dates can be used):

dateObj1 >= dateObj2 // dateObj1 is at a later date than dateObj2