PHP MySQL使用$ThisYear参数查询年份失效问题求助

• 变量类型不匹配：你用strtotime($ThisYear)把date("Y")得到的4位年份字符串（比如"2024"）转换成了Unix时间戳（比如1704067200），而YEAR(TransDate)返回的是4位数字年份，两者数值完全不同，自然查不到数据。
• PHP变量解析失效：SQL语句使用单引号'包裹，PHP单引号字符串中的变量$ThisYear不会被解析，会直接以字符串"$ThisYear"的形式传入MySQL，和数据库中的年份数值不匹配。

方案1：修复变量类型并正确嵌入字符串（适合简单场景） #

去掉多余的strtotime转换，同时把SQL语句改成双引号包裹（PHP双引号会解析内部变量）：

$ThisYear = date("Y"); 
// 去掉strtotime转换，直接保留4位年份字符串
$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";

注意：这种方式存在SQL注入风险，仅适合内部系统或已做严格输入校验的场景。

方案2：使用预处理语句（推荐，安全防注入） #

用PDO或mysqli的预处理语句，避免直接拼接变量，同时保证类型匹配：

PDO示例 #

$ThisYear = date("Y");
$pdo = new PDO("mysql:host=localhost;dbname=your_db", "user", "pass");
$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) = ?  
        group by DATE_FORMAT(TransDate, '%M %Y') 
        order BY YEAR(TransDate), MONTH(TransDate) desc";
$stmt = $pdo->prepare($sql2);
$stmt->execute([$ThisYear]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

mysqli示例 #

$ThisYear = date("Y");
$conn = mysqli_connect("localhost", "user", "pass", "your_db");
$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) = ?  
        group by DATE_FORMAT(TransDate, '%M %Y') 
        order BY YEAR(TransDate), MONTH(TransDate) desc";
$stmt = mysqli_prepare($conn, $sql2);
mysqli_stmt_bind_param($stmt, "s", $ThisYear);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$results = mysqli_fetch_all($result, MYSQLI_ASSOC);

额外优化建议 #

可以直接用日期范围查询代替YEAR(TransDate)函数，这样能让数据库使用TransDate字段的索引，提升查询性能：

WHERE TransDate >= ? AND TransDate < ?

对应的PHP变量处理：

$startOfYear = date("Y-01-01 00:00:00");
$endOfYear = date("Y+1-01-01 00:00:00");
// 然后在预处理语句中绑定这两个变量

内容的提问来源于stack exchange，提问作者Wilest