SQLite PDO绑定不起作用? [英] SQLite PDO bindings not working?

问题描述

我觉得我对这件事失去了理智.我有三个简单的表.一个用户表，一个角色表和一个role_user表，该表以多对多关系将用户和角色连接起来.

I feel like I'm losing my mind on this one. I have three simple tables. A user table, a roles table, and a role_user table that joins the user and roles in a many to many relationship.

对于用户角色，我具有以下代码:

I have the following code to the roles for a user:

$query = $pdo->prepare('select roles.* from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = ?');
$query->execute(array('1'));
die(var_dump($query->fetchAll()));

这将返回一个空数组.没结果.但是，如果我将代码更改为此，则将获得用户的角色:

This returns an empty array. No results. However, if I change the code to this, I will get the user's roles:

$query = $pdo->prepare('select roles.* from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = 1');
$query->execute();
die(var_dump($query->fetchAll()));

我缺少完全显而易见的东西吗?我的SQL是否有一些混乱的绑定?为什么带有绑定的示例不起作用?

Am I missing something totally obvious? Is there something about my SQL that is messing up the bindings? Why doesn't the example with bindings work?

推荐答案

这是PDO中的错误:

This is a bug in PDO: http://bugs.php.net/bug.php?id=45259

作为一种解决方法，以下代码虽然较重，但应在PHP5.3中工作:

As a workaround, the following code, though heavier, should work in PHP5.3:

$query = $pdo->prepare(
    'select roles.* from roles inner join role_user on roles.id = role_user.role_id '
    . 'where role_user.user_id = :id'
);
$query->bindValue(':id', 1, PDO::PARAM_INT);
$query->execute();
die(var_dump($query->fetchAll()));

SQLite的最新版本具有本机准备的语句，但是我认为PDO尚不能使用它们(IIRC，PDO的代码没有真正的维护者，因此它不会发展太多).它可能不会改变任何东西，但是您仍然可以尝试使用$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

The latest versions of SQLite have native prepared statements, but I don't think PDO can use them yet (IIRC, PDO's code has no real maintainer, so it does not evolve much). It probably won't change anything, but you could still try to disable the emulation with $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

这篇关于SQLite PDO绑定不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！