pg_query_params query for null values

Question

I have this query which returns the matching rows. I need to ignore the condition for which the variable is null ex. :

If $data[0] is null, it would become

$parameters = array ($data[1], $data[2]);       
$result = pg_query_params(connect(), 'SELECT * FROM person WHERE surname= $1 and status=$2', $parameters);

If $data[0] and $data[2] are null, it would become:

$parameters = array ($data[1]);         
$result = pg_query_params(connect(), 'SELECT * FROM person WHERE surname= $1', $parameters);

And so on ...

This is the current query:

$parameters = array ($data[0], $data[1], $data[2]);         
$result = pg_query_params(connect(), 'SELECT * FROM person WHERE name = $1 and surname= $2 and status=$3', $parameters);

I read about coalesce() but don't know how to include the parameters $1, $2, $3.

If $date[0] is null, you could add this to your query: OR NULL IS $1 — Frank Heikens, Feb 17 '15 at 13:19
@FrankHeikens: Sorry, I realized I hadn't asked the question correctly and made a modification. — Lazarus Rising, Feb 17 '15 at 13:21
Simple solution: Create 3 pieces of SQL and just check how many parameters $data has to select the right piece of SQL. Otherwise you need some dynamic SQL, created by your PHP-script — Frank Heikens, Feb 17 '15 at 13:34
@FrankHeikens: Actually, to do it like you are suggesting, I would need pow(2, n) queries, where n is the number of parameters that $data has. Which is 8 in this case and 16 in another case I'm working. As for the dynamic sql, this is what I'm not figuring out how to create. — Lazarus Rising, Feb 17 '15 at 13:41

score 2 · Accepted Answer · answered Feb 17 '15 at 16:33

2

It's a classic problem: building a query with a WHERE clause that filters from a set of optional criteria. Here's how you may implement in a rather generic way, to accomodate any combination of columns.

First the input parameters need to be connected to the column names in PHP so that you can programmatically address them. We may use an associative array for that:

$columns = array("name"=>$data[0], "surname"=>$data[1], ...etc...);

Then loop over them to generate (into an array) each individual equality test (colname=$N) for non-null inputs:

$clauses = array();
$non_null_params = array();
$param_index=1;
foreach ($columns as $name=>$value) {
  if ($value !== null) {
    $clauses[] = "$name=\${$param_index}";
    $param_index++;
    $non_null_params[] = $value;
  }
}

If there are no resulting clauses at all, either there's no WHERE filtering or you want generate an error, depending on the requirements.

   if (empty($clauses)) {
      $where_clause = "";  // or error out?
   }

Otherwise glue the individual colname=$N clauses together into a query fragment:

$where_clause = "WHERE " . implode(" AND ", $clauses);

And eventually run the dynamic query with the set of non-null parameters:

pg_query_params("SELECT some_columns $where_clause", $non_null_params);

answered Feb 17 '15 at 16:33

Daniel Vérité

58,074
15
129
156

In the fifth line of the code where you create the query you must have accidentally added an extra '=' symbol. I assume you meant if ($value != null) – Lazarus Rising Feb 17 '15 at 17:07
Thanks, that was what I looking for. However, it only works in the cases when all fields are null or when all fields are not null, but not when some are null and others aren't. Once I'm done fixing it, I will accept your answer and add comments with the modifications. – Lazarus Rising Feb 17 '15 at 17:08
@amygrimaldi: the choice of the `!==` operator as opposed to `!=` is intentional and important. If you have `$a=0` then `$a!=null` will not be taken whereas `$a!==null` will be taken because 0 and null are not the same thing. More generally see [this answer](http://stackoverflow.com/a/80649/238814) with its priceless comparison matrix of both operators. – Daniel Vérité Feb 17 '15 at 17:21
Also I disagree with your claim that it doesn't work on a mix of null and non-null params. The whole point of the foreach loop is to look at them one by one and take appropriate action for each. – Daniel Vérité Feb 17 '15 at 17:24
Well, I know the difference between these two operators. But since I'm not getting any value equal to 0 (im checking the values in another part of the code), its not an issue. Also, for some reason, $where_clause is always equal to WHERE name=$1 AND surname=$2 AND status=$3 or it is empty (I wrote it in a file to see what was going on). And if any of the parameters is null, $non_null_params still has an entry of it equal to the name (name, value or status) – Lazarus Rising Feb 18 '15 at 09:54
OK, I am sorry to trouble you. Your code was fine. But I was getting the values from select-boxes, Therefore, Even if I didn't select anything, the first option was automatically selected. That causes extra clauses which made the query in most of the cases false ... It took me a couple of print_r() and echo to figure it out due to the very stupid entries I had recently put in my select-boxes. – Lazarus Rising Feb 18 '15 at 10:01

pg_query_params query for null values

1 Answers1