mysql - PHP PDO Prepared statement bind NULL value

Question

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.

My code so far:

$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();

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

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.

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

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

Thankful for any help.

Answer 1

This is your query:

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;

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;