PDO unable to bindValue for NULL in SELECT query

Question

I have read many many posts on SO and several accepted answers that don't work for me. I have been looking at the code for so long I can't see straight anymore (over several weeks).

I need to query a MySQL database table based on 2 columns. The second column can contain a number or be NULL. The most accepted answer I found was this: bindValue(':terri', null, PDO::PARAM_INT);, but it doesn't work for me and for the life of me I can't see why.

Query:

 $sql = "SELECT s.`id`, s.`php_tz`, t.`offset`, t.`description`
         FROM `{$pdo->db_name}`.`timezones_std` AS s
         INNER JOIN `{$pdo->db_name}`.`timezones` AS t ON s.`std_tz_id` = t.`id`
         WHERE s.`country_id` = :ctry AND s.`territory_id` = :terri
         ORDER BY t.`offset` ASC, t.`description` ASC, SUBSTRING_INDEX(s.`php_tz`, '/', -1) ASC;
        ";

Bind:

$pdo->bind(":terri", !empty($_POST["terri"]) ? $_POST["terri"] : null, PDO::PARAM_INT);

Bind Function:

 public function bind($param, $value = null, $type = null)
 {
  if(is_array($param) && is_null($value) && is_null($type))
  {
   foreach($param as $key => $value)
   {
    $this->bind($key, $value);
   } // Closing brace for foreach($param as $key => $value)
  } // Closing brace for if(is_array($param) && is_null($value) && is_null($type))
  else
  {
   if(is_null($type))
   {
    switch(true)
    {
     case is_int($value):
      $type = PDO::PARAM_INT;
      break;
     case is_bool($value):
      $type = PDO::PARAM_BOOL;
      break;
     case is_null($value):
      $type = PDO::PARAM_NULL;
      break;
     default:
      $type = PDO::PARAM_STR;
      break;
    }
   }
   $this->stmt->bindValue($param, $value, $type);
  } // Closing brace for if(is_array($param) && is_null($value) && is_null($type)) else
 }

I know my bind function contains a is_null case but that should not be in play because I pass the 3rd parameter. As posted the code runs with no errors but does not return the correct records.

Can anyone see what I can't.

Thanks for any and all help,

Charles

But aren't you specifically telling the 'bind' to use INT with that 3rd parm? If your value is actually a null and you tell it to bind an INT, then I think the bindValue won't raise an error -- it just silently fails. Are you actually getting data back, but just not the right stuff? — McAuley, Dec 10 '20 at 23:42
No data returned at all. I used the INT because most of the accepted answers say I must. But I have used PDO::PARAM_NULL also and it still doesn't work. — CharlesEF, Dec 10 '20 at 23:57
If I change the query to use MySQL IS NULL then I can get the correct records returned. I'd rather not have to do this. — CharlesEF, Dec 11 '20 at 00:09
**WARNING**: Whenever possible use **prepared statements with placeholder values** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. — tadman, Dec 11 '20 at 04:54
For the goodness sake, **get rid** of this bind function, and send all your parameters straight into execute(). It will not only solve this problem but also prevent some odd but disastrous consequences. — Your Common Sense, Dec 11 '20 at 07:13
And your problem is just a wrongly formulated question. It is not that you cannot *bind* a null value - it binds all right which you could have verified yourself. The problem is that a condition `territory_id = null` straight up makes no sense and will never return positive. — Your Common Sense, Dec 11 '20 at 07:18
Boy, I thought I was doing things right. So I take it I'm going to have to use 'IS NULL' after all. I'll do some research on the other suggestions. Thanks for the help. BTW, I can bind NULL for an INSERT or UPDATE just not for a SELECT, which is why I posted. — CharlesEF, Dec 11 '20 at 19:41
@Your Common Sense, I'm confused. In 1 comment you say send all your parameters into execute() and that will solve my problem and others (still doing research on this). Then your 2nd comment says 'territory_id = null' will never work. I take it then I must use IS NULL? Or is there another way to select records based on 'col1 = xx AND col2 = null'. — CharlesEF, Dec 12 '20 at 02:24
My first comment is related to The function bind(), not your immediate problem. As of the problem there is a link at the top of your question with the solution — Your Common Sense, Dec 12 '20 at 05:32

PDO unable to bindValue for NULL in SELECT query

0 Answers0