Home > Software engineering >  php mysql Select year with parameter
php mysql Select year with parameter

Time:10-23

Struggling with the following code which is not working ($ThisYear paramater)

$ThisYear = date("Y"); 
$ThisYear = strtotime($ThisYear);
$sql2 = 'SELECT 
            distinct 
            DATE_FORMAT(TransDate, "%M %Y") as MonthYear
            , DATE_FORMAT(TransDate, "%Y") as TransYear
            , DATE_FORMAT(TransDate, "%m") as TransMonth 
        from Leave 
        where YEAR(TransDate) = **"$ThisYear"**  
        group by DATE_FORMAT(TransDate, "%M %Y") 
        order BY YEAR(TransDate), MONTH(TransDate) desc';

versus this which is working:

$ThisYear = date("Y"); 
$sql2 = 'SELECT 
            distinct 
            DATE_FORMAT(TransDate, "%M %Y") as MonthYear
            , DATE_FORMAT(TransDate, "%Y") as TransYear
            , DATE_FORMAT(TransDate, "%m") as TransMonth 
        from Leave 
        where YEAR(TransDate) = **2022** 
        group by DATE_FORMAT(TransDate, "%M %Y") 
        order BY YEAR(TransDate), MONTH(TransDate) desc';

CodePudding user response:

According to PHP documentation, strtotime() parse about any English textual datetime description into a Unix timestamp.

In order:

  • Your variable was converted into a number (1666484520 Unix timestamp for 2022).
  • Server compared the number of the year to your variable = 1666484520.

That's the right query and variable.

$ThisYear = date("Y"); 
$sql2 = 'SELECT 
            distinct 
            DATE_FORMAT(TransDate, "%M %Y") as MonthYear
            , DATE_FORMAT(TransDate, "%Y") as TransYear
            , DATE_FORMAT(TransDate, "%m") as TransMonth 
        from Leave 
        where YEAR(TransDate) = $ThisYear 
        group by DATE_FORMAT(TransDate, "%M %Y") 
        order BY YEAR(TransDate), MONTH(TransDate) desc';
  • Related