SQL查询无法在php pdo中运行

This returns results:

这会返回结果:

$query = $dbh->prepare('SELECT * FROM groups WHERE id LIKE :id ORDER BY id');
$query->bindValue(':id', $this->id.'_');

This does not:

这不是:

$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE :id AND d.desc_type=1 ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

And yet if I run the SQL query below in WebMin ("0002_" is the value being bound in the above), then I get results.

然而,如果我在WebMin中运行下面的SQL查询(“0002_”是上面绑定的值),那么我得到结果。

SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE "0002_" AND d.desc_type=1 ORDER BY g.id

So why does the PDO dislike the query in my second piece of code above?

那么为什么PDO不喜欢上面第二段代码中的查询呢?

2 个解决方案

#1

Okay, I nailed it:

好的,我钉了它:

As I said in my initial question, the code below doesn't return results:

正如我在最初的问题中所说,下面的代码不会返回结果:

$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id WHERE g.id LIKE :id AND d.desc_type=1 ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

This code returns the expected results:

此代码返回预期结果:

$query = $dbh->prepare('SELECT g.*, d.desc_text FROM groups g LEFT JOIN descriptions d ON d.desc_id=g.id AND d.desc_type=1 WHERE g.id LIKE :id ORDER BY g.id');
$query->bindValue(':id', $this->id.'_');

Strangely, either SQL query will return results when run from within Webmin, but it seems that the PHP PDO doesn't like having the "AND d.desc_type=1" as part of the WHERE clause and needs it in the LEFT JOIN ON clause.

奇怪的是,从Webmin内部运行时,SQL查询都会返回结果,但似乎PHP PDO不喜欢将“AND d.desc_type = 1”作为WHERE子句的一部分,并且需要在LEFT JOIN ON子句中使用它。

#2

-1

why does the PDO dislike the query in my second piece of code above?

为什么PDO不喜欢上面第二段代码中的查询?

Ask PDO:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

If PDO doesn't throw any errors - it "likes" your query and has nothing to do with the problem. Check your data, database and typos.

如果PDO没有抛出任何错误 - 它“喜欢”你的查询并且与问题无关。检查您的数据,数据库和拼写错误。

As a final check omit bindvalue part

最后检查省略bindvalue部分

$stmt = $dbh->prepare('put your query from WEBMIN here');
$stmt->execute();
$res  = $stmt->fetch();
var_dump($res);

if it retirns any data - blame PDO
if not - check your data, database and typos.

如果它重新计算任何数据 - 如果不是,则责备PDO - 检查您的数据,数据库和拼写错误。

#1