mysql查询使用年份的日期

Question

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.