如何将INTERVAL参数与PDO绑定? [英] How do I bind an INTERVAL param with PDO?

问题描述

我有一个查询，我试图完全从绑定参数中进行设置，但是现在我不得不诉诸于从GET参数中添加字符串(我真的不想这样做).

I have a query that I am trying to set up entirely from bound parameters, but right now I'm having to resort to appending a string from a GET parameter (I don't really want to do that).

这是我现在的解决方法:

Here's my solution right now:

$interval = !empty($_REQUEST['interval']) ? $_REQUEST['interval'] : '28 DAY';
$interval = str_replace('_', ' ', $interval);

$data = array(
    ':msisdn' => $_REQUEST['msisdn']
);

$sql = <<<SQL
SELECT
    COUNT(*) AS `countup`
FROM
    (
        SELECT
            utc.`id_user`
        FROM
            `user_to_cli` utc
        WHERE
            1=1
            AND utc.`cli` = :msisdn
            AND utc.`dts_start` > CURDATE() - INTERVAL $interval
    ) a
;
SQL;

这很好用，但是当我尝试将时间间隔更改为绑定参数时，像这样:

This works fine, but when I try to change the interval to a bound parameter, like so:

$interval = !empty($_REQUEST['interval']) ? $_REQUEST['interval'] : '28 DAY';
$interval = str_replace('_', ' ', $interval);

$data = array(
    ':msisdn' => $_REQUEST['msisdn'],
    ':interval' => $interval
);

$sql = <<<SQL
SELECT
    COUNT(*) AS `countup`
FROM
    (
        SELECT
            *
        FROM
            `user_to_cli` utc
        WHERE
            1=1
            AND utc.`cli` = :msisdn
            AND utc.`dts_start` > CURDATE() - INTERVAL :interval
    ) a
;
SQL;

我收到以下错误:

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') a'

推荐答案

您的代码尝试使用绑定变量来替换SQL关键字和数字.你不能那样做&贸易；.

Your code tries to use a bind variable to replace a SQL keyword as well as a number. You Can't Do That™.

解决此问题后，我将时间间隔转换为秒，然后使用

When I've solved this problem I've translated my time intervals to seconds then used

 CURDATE() - INTERVAL :seconds SECONDS

顺便说一句，CURDATE()给您今天的午夜.如果要处理次日间隔，则可能需要改为NOW().

By the way, CURDATE() gives you midnight today. You may want NOW() instead if you're handling sub-day intervals.

这篇关于如何将INTERVAL参数与PDO绑定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！