0

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?

lman
  • 9
  • 1
  • 2
  • 2
    Should it not be individual quotes? – Script47 Sep 11 '17 at 13:07
  • What are you trying to do? Get the results where it contains that provider ID or if the provider id is null? – aynber Sep 11 '17 at 13:08
  • 2
    You're using [`IFNULL`](https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull) but there's no parameter which can ever be null in there. – apokryfos Sep 11 '17 at 13:10
  • Show sample data for column `provider_id` moreover in `IN` clause you should separate each value not like "131, 132", it will be treated as single value not 2 values – M Khalid Junaid Sep 11 '17 at 13:10
  • I am using dynamic filter (represented by variable $pdo_test). It may have values and it may not depending on user filtering. The IFNULL handles the situation when $pdo_test = null (no filter applied): "SELECT provider_id FROM provider WHERE provider_id IN ( IFNULL($pdo_test, '999') )"; – lman Sep 11 '17 at 13:14
  • Then no. Do that check before passing it into your query, because it's not going to work the way you want. – aynber Sep 11 '17 at 13:16
  • Really not possible this way? Even with PDO? Thanks – lman Sep 11 '17 at 13:18

3 Answers3

0
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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Sagar Gangwal
  • 7,544
  • 3
  • 24
  • 38
0

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks for answer, statement is designed to work with dynamic condition filtering, which may have values: null, one value or multiple value. So my approach is instead messing with php code, rather to first validate if the condition is null, if is not null, then the IFNULL should pass the condition to parent function IN. But then I have the quotes mismatch... Same with PDO – lman Sep 11 '17 at 13:27
0

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
apokryfos
  • 38,771
  • 9
  • 70
  • 114
  • Hi, I showed example with values 131 and 132 for illustration only. Values are carried by variable which VALUE may be null. I want to find way without PHP validation as the complete SQL statement is more complex than my example. – lman Sep 11 '17 at 13:37
  • @Iman I stand by my suggestion that you should let PHP deal with PHP variables because they are not the responsibility of the database to deal with. However I am also going to suggest you provide an example which more accurately describes the complexity of your use case in order to get more sensible answers. – apokryfos Sep 11 '17 at 13:39
  • Thank you @apokryfos, seems to be the way to go. Thank you all. – lman Sep 11 '17 at 13:50