mysql query using date of year

Question

hi to all

I have a problem for querying records into mysql

explanation below The user could pick a date to assign in variable fromdate and todate

example.

$fromdate = 2008/01/01
$todate = 2009/10/31

In above case, i have to loop and query with the ff:

SELECT * FROM table where date BETWEEN '2008/01/01' AND '2008/12/31';

second loop

SELECT * FROM table where date BETWEEN '2009/01/01' AND '2009/10/31';

if $fromdate and $todate is more than one year then I have to limit my query per year. But if less than one year then use $fromdate and $todate in the query.

Thanks in advance

Tirso

Answer 1

You can use something like this to calculate the date.

$d1 = new DateTime($todate);
$d2 = new DateTime($fromdate);
$d2->modify("+1 year");
$min = min($d1, $d2);
$todate = $min->format('Y/m/d');

If you want to paginate the results, you will have to modify $fromdate on each page before doing the $todate calculation. For example, if you are on the second page, first load $fromdate into a DateTime object and add one year, if you are on the third page, add two years, etc.

Answer 2

Not entirely sure what you mean but can you not use the mysql Year function?

something like:

SELECT * FROM table WHERE YEAR(`yourdatefield`) = '2008'

or

SELECT * FROM table WHERE YEAR(`yourdatefield`) BETWEEN '2008' AND '2009';

Answer 3

First and foremost, the format of dates in MySQL is YYYY-MM-DD (not YYYY/MM/DD), so make sure you do that right. That might be why you are seeing wrong results.

BETWEEN works very well with (correctly formatted) dates. Example query:

SELECT * FROM table WHERE date BETWEEN '2008-05-12' AND '2009-04-23';