PHP PDO Prepared 语句绑定 NULL 值 [英] PHP PDO Prepared statement bind NULL value

问题描述

我正在尝试将 SQL 查询作为准备好的语句运行 - 我尝试绑定一个 NULL 值.嗯，我已经在网上做了一些研究，是的，我已经在 stackoverflow 上找到了所有已知的主题.

I'm trying to run a SQL query as a prepared statement - and I try to bind a NULL value. Well I've done some research on the web and yes, I already found all the known topics here on stackoverflow.

到目前为止我的代码:

$stmt = $db->prepare("SELECT c.*, COUNT(d.servername) as servercount, d.controller FROM customers C JOIN customerdata d ON c.id = d.customer WHERE isVdi = :isVdi AND d.controller = :controller GROUP BY d.customer ORDER BY c.name ASC, c.environment ASC");
            $stmt->bindValue(':isVdi', $isVdi);
            $stmt->bindValue(':controller', null, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll();

但这行不通.我得到一个空的结果数组.当我用 controller IS NULL 替换 controller = :controller 时，它工作得很好.

But this doesn't work. I get an empty result array. When I replace the controller = :controller by controller IS NULL it works perfectly.

最后，我想将参数绑定到 :controller 上的变量，但现在我正试图直接将 NULL 写入其中，因为这甚至不起作用.

At the end, I would like to bind the param on :controller from a variable, but right now I'm trying to directly write the NULL into it, since that doesn't even work.

我在这里找到了这种方式:如何使用 PDO 插入 NULL 值?

I found this way here: How do I insert NULL values using PDO?

我也已经尝试过 PDO::PARAM_NULL 和所有这些东西 - 没有任何效果.我真的不明白.

I also already tried with PDO::PARAM_NULL and all that stuff - nothing works. I really don't get it.

感谢您的帮助.

推荐答案

这是您的查询:

SELECT c.*, COUNT(d.servername) as servercount, d.controller
FROM customers C JOIN
     customerdata d
     ON c.id = d.customer
WHERE isVdi = :isVdi AND d.controller = :controller
GROUP BY d.customer
ORDER BY c.name ASC, c.environment ASC;

不幸的是，任何 = NULL 都不会返回 true.更不幸的是，MySQL 不支持 ANSI 标准 NULL - 安全比较器 与 没有区别.但令人高兴的是，它有另一种选择.你可以试试这个:

Unfortunately, anything = NULL is never going to return true. Even more unfortunately, MySQL does not support the ANSI standard NULL-safe comparator is not distinct from. But happily it has an alternative. You can try this:

SELECT c.*, COUNT(d.servername) as servercount, d.controller
FROM customers C JOIN
     customerdata d
     ON c.id = d.customer
WHERE isVdi = :isVdi AND
      d.controller <=> :controller
GROUP BY d.customer
ORDER BY c.name ASC, c.environment ASC;

这篇关于PHP PDO Prepared 语句绑定 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！