当前位置:  开发笔记 > 编程语言 > 正文

在PHP和PostgreSQL中使用带间隔的命名占位符失败

如何解决《在PHP和PostgreSQL中使用带间隔的命名占位符失败》经验,为你挑选了0个好方法。

好的,我已经确认可以在PHP上正常使用。

$ php --version
PHP 5.6.16 (cli) (built: Dec 30 2015 15:09:50) (DEBUG)


pdo_pgsql
PDO Driver for PostgreSQL   enabled
PostgreSQL(libpq) Version   9.4.0
Module version  1.0.2
Revision    $Id: fe003f8ab9041c47e97784d215c2488c4bda724d $

我想使用PDO在PHP中重新创建以下SQL:

UPDATE relationships SET status = 4 WHERE created > NOW() - interval '2 seconds';

该脚本正在工作:

prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval '?'");
$stmt->execute(array("2 seconds"));

这里是命名占位符:

prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval ':blah'");
$stmt->execute(array(":blah" => "2 seconds"));

出现此错误:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: :blah in ... line 5

现在根据PHP文档,

Example#6无效使用占位符:

 prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
 $stmt->execute(array($_GET['name']));

 // placeholder must be used in the place of the whole value
 $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
 $stmt->execute(array("%$_GET[name]%"));
 ?>

这是更新的代码:

prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - :blah");
$stmt->execute(array(":blah" => "interval '2 seconds'"));

产生以下数据库错误(无脚本错误):

ERROR:  operator does not exist: timestamp with time zone > interval at character 51
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  UPDATE relationships SET status = 4 WHERE created > NOW() - $1

PDO在这里做一些奇怪的事情,因为:

# select NOW() - interval '2 seconds' as a , pg_typeof(NOW() - interval '2 seconds') as b;
               a               |            b             
-------------------------------+--------------------------
 2015-12-30 18:02:20.956453+00 | timestamp with time zone
(1 row)

那么如何在PostgreSQL和interval中使用命名占位符?

推荐阅读
Chloemw
这个屌丝很懒,什么也没留下!
DevBox开发工具箱 | 专业的在线开发工具网站    京公网安备 11010802040832号  |  京ICP备19059560号-6
Copyright © 1998 - 2020 DevBox.CN. All Rights Reserved devBox.cn 开发工具箱 版权所有