使用PHP PDO准备的语句和MySQL选择字段为空的行 [英] Selecting rows where a field is null using PHP PDO prepared statements and MySQL

问题描述

我一直在将应用程序转换为使用PDO准备的语句而不是mysqli，但是我遇到了一个奇怪的问题.我在数据库中有一些记录，期望该字段为空.不是'null'(字符串)或''(空字符串)，而是NULL.我动态地构建查询，所以在过去，当我在对象中遇到null变量时，我会像这样构建查询:

I've been converting an app to use PDO prepared statements rather than mysqli and I'm running into a strange problem. I have some records in the database where it's expected that a field will be null. Not 'null' (string), or '' (empty string), but NULL. I build my queries dynamically, so in the past when I came across a null variable in an object, I'd build the query like this:

WHERE fieldName is null;

当该字段为空时，将获得预期的结果.

And would get the expected results when the field was null.

现在有了PDO，我的查询没有返回任何结果，也没有出现任何错误.只是根本没有返回我期望的记录.当我回显生成的查询并直接在MySQL中运行它们时，我得到了预期的结果，但是在应用程序内未返回任何结果.

Now with PDO, my queries aren't returning any results and I'm not getting any errors. It just simply isn't returning the records I would expect. When I echo the built queries and run them directly in MySQL I get the expected results, but within the application there are no results returned.

我尝试过的一些事情包括建立如下所示的查询:

Some of the things I've tried include building queries that look like this:

WHERE fieldName is null;

或

WHERE fieldName <=> null;

我还尝试了以下标准的准备声明:

I have also tried the standard prepared statement of:

WHERE fieldName = :fieldName

，然后绑定这些类型的语句:

and then binding with these kinds of statements:

$stmt->bindParam(":$field", $value);
$stmt->bindParam(":$field", $value, PDO::PARAM_NULL);
$stmt->bindParam(":$field", null, PDO::PARAM_NULL);
$stmt->bindValue(":$field", null, PDO::PARAM_NULL);
$stmt->bindValue(":$field", null, PDO::PARAM_INT);

任何对此的帮助将不胜感激.我的PHP版本是5.3.10，而MySQL是5.5.22.作为附带的问题，我仍然不清楚bindParam和bindValue之间的区别，因此，如果有必要在您的答案中包括我，我将非常感谢您对此主题进行的澄清...

Any help with this would be greatly appreciated. My PHP version is 5.3.10 and MySQL is 5.5.22. As a side question, I still am not clear on the difference between bindParam and bindValue, so if it makes sense to include in your answer I would really appreciate some clarification on the subject...

推荐答案

原来是对此问题的一些回答的组合:

Turns out to have been a combination of some of the responses to this question: How do I insert NULL values using PDO? (as John pointed out)

我需要将where子句更改为此:

I needed to change my where clause to this:

WHERE fieldName is :fieldName;

然后将一个字段=>值的数组传递给执行函数:

And then pass an array of field => value to the execute function:

$binds = array();
foreach ($wheres as $where) {
    $field = $where['field'];
    $value = $where['value'];
    $binds[":$field"] = $value;
}
$stmt->execute($binds);

即使更改了我的where子句，该查询也根本无法与bindValue一起使用.有人知道为什么绑定不起作用，而另一种方法却起作用吗?

The query wouldn't work at all with bindValue, even having changed my where clause. Does anybody know why binding doesn't work, yet this other method did?

这篇关于使用PHP PDO准备的语句和MySQL选择字段为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！