问题描述
我一直在将应用程序转换为使用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...
推荐答案
原来是对此问题的一些回答的组合:(如John指出的那样)
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选择字段为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！