2

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.

Twinfriends
  • 1,972
  • 1
  • 14
  • 34
  • 2
    am not quite sure about this, but the uppercase "C" here `FROM customers C` and using a lowercase "c" afterwards could be an issue. On a \*NIX OS, it is case-sensitive. – Funk Forty Niner Feb 06 '18 at 13:17
  • @Syscall OP already tried that as stated in the question – Rotimi Feb 06 '18 at 13:18
  • @FunkFortyNiner While I agree that this isn't really good, its 100 % not the problem. I changed it now, it still doesn't work. Btw: The query worked before I added the `AND d.controller = :controller` part - so that was no issue at all. – Twinfriends Feb 06 '18 at 13:18
  • that's why I posted that as a comment ;-) @Twinfriends which I edited for future readers. – Funk Forty Niner Feb 06 '18 at 13:19
  • Just wondering if you're using PDO's error handling on the query. https://php.net/manual/en/pdo.error-handling.php - That may have shot something out. Gordon's answer makes a lot of sense though, give that a shot. – Funk Forty Niner Feb 06 '18 at 13:22
  • @FunkFortyNiner Yeah I'm using PDO's error handling. I write every error to some logfiles, but that query didn't gave me any error nor a warning. It just returned an empty array. Also Gordon's answer worked like a charm. – Twinfriends Feb 06 '18 at 13:25
  • Cool, glad to hear it :-) @Twinfriends – Funk Forty Niner Feb 06 '18 at 13:26

1 Answers1

6

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That worked, I'll accept it in a few minutes. I'm kinda confused about that syntax, but I'll take it anyway. Thats what I thought, doing somehint `= NULL` is not good syntax for SQL, but I didn't see another way. – Twinfriends Feb 06 '18 at 13:22
  • 1
    @Twinfriends . . . https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to. – Gordon Linoff Feb 06 '18 at 13:31
  • Thanks a lot for the reference ;) – Twinfriends Feb 06 '18 at 13:38