MySQL WHERE IN condition with IFNULL function

Question

I have problem with IFNULL function used within WHERE IN condition:

SELECT provider_id FROM provider WHERE provider_id IN ( IFNULL("131, 132",'999') )

I tried all quoting alternatives like:

 SELECT provider_id FROM provider WHERE provider_id IN ( IFNULL("'131', '132'", '999') )

But can not get this done with PHP.

Could you please suggest how to correctly format the multi-value parameter within the IFNULL function?

Answer 1

First of all your question is using IFNULL on not null values which makes no sense.

Secondly IFNULL would return a single value, it is unnecessary to do an provider_id IN (...) for a single value since it would be the same as provided_id=...

Lastly, if you want to check if a PHP variable is null then have PHP deal with it.

//$pdo_test
$query = "SELECT provider_id FROM provider";
if ($pdo_test !== null) {    
   $query = " WHERE provider_id=?"
}

Then you can make the query:

$stmt = $pdo->prepare($query);
if ($pdo_test !== null) {  
   $stmt->bindParam(1,$pdo_test,PDO::PARAM_INT); //Looks like an int, but change if its something else
} 
//Code like  $stmt->fetch() and processing goes here

Answer 2

SELECT provider_id FROM provider WHERE provider_id IN ('131', '132', '999')

Simply try above code.

You don't need any extra quote or something to execute your query.

Answer 3

The IFNULL does not take a "multi-value parameter". There is no way to correctly format it.

The IFNULL function takes only two arguments. If the first argument evaluates to NULL, the function returns the second argument. Otherwise, it returns the first expression.

That is, the SQL expression

  IFNULL(foo,'bar')

is equivalent to

  CASE WHEN foo IS NULL THEN 'bar' ELSE foo END

---

This:

SELECT IFNULL("131, 132",'999')

returns:

IFNULL("131, 132",'999')  
--------------------------
131, 132                  

And this:

provider_id IN ( IFNULL("131, 132",'999') )

is equivalent to:

provider_id = '131, 132'

---

It's not clear what you are trying to accomplish, or why an IFNULL function is included in the statement.

Back up a step, and more clearly express the problem you are trying to solve.