Selecting rows where a field is null using PHP PDO prepared statements and MySQL
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.
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;
or
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);
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)
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);
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?
Different question, but all awnsers are in here: How do I insert NULL values using PDO?
To sum it up:
$stmt->bindValue(':param', null, PDO::PARAM_INT);
Should work.You can also use:
$stmt->execute( array(":param" => NULL));
For bindparam you must give the value in a variable (not a constant), bindvalue you can use constants etc.
In MySql you can use the query
select * from atable where isnull(column_name);
to check for null values.
链接地址: http://www.djcxy.com/p/11438.html上一篇: 我如何测试Haskell中的错误？