PHP中SQLite预编译查询strftime传日期参数无返回如何解决

问题根源 #

PDO_SQLITE执行参数绑定时默认会自动识别输入值的类型，如果你传入的日期字符串（如2021-10-1）被误判为数值类型传入SQLite，strftime接收到非字符串格式的入参会返回NULL，最终WHERE条件不成立，查询返回空数组。

最优解决方案（推荐） #

直接在PHP侧完成日期到Unix时间戳的转换，不需要在SQL层调用strftime处理，既避免了绑定类型问题，也能提升查询效率：

function data($date, $name) {
    // 先转换日期为时间戳，同时做非法日期校验
    $timestamp = strtotime($date);
    if ($timestamp === false) {
        return [];
    }
    $query = "SELECT
             price,
             datetime(time.time, 'unixepoch', 'utc') as date, 
             shop.name
           FROM
             price
           LEFT JOIN product on price.product = product.id
           LEFT JOIN shop on shop.id = price.shop
           LEFT JOIN time on price.time = time.id
           WHERE product.name = ? and time.time > ?
           ORDER BY time.time";
    return query($query, array($name, $timestamp));
}

可选方案（保留SQL层strftime调用） #

如果你需要保留在SQL中处理日期的逻辑，就需要手动绑定参数并强制指定为字符串类型，避免PDO自动转换类型：

function data($date, $name) {
    global $db;
    $query = "SELECT
             price,
             datetime(time.time, 'unixepoch', 'utc') as date, 
             shop.name
           FROM
             price
           LEFT JOIN product on price.product = product.id
           LEFT JOIN shop on shop.id = price.shop
           LEFT JOIN time on price.time = time.id
           WHERE product.name = ? and time.time > strftime('%s', ?)
           ORDER BY time.time";
    $stmt = $db->prepare($query);
    // 手动绑定参数，指定字符串类型
    $stmt->bindValue(1, $name, PDO::PARAM_STR);
    $stmt->bindValue(2, $date, PDO::PARAM_STR);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

额外注意 #

你原有代码中响应头的拼写有误，header('cotent-type: application/json'); 应改为 header('Content-Type: application/json');，否则前端无法正确识别JSON格式响应。

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