MySQL check if null if value is empty using php

Question

I have multiple arrays similar to below.

{
  ["sku_original"]=>
  string(33) "98iuoo"
  ["po"]=>
  string(5) "9897"
  ["ca"]=>
  string(3) "557"
  ["cl"]=>
  string(5) "33"
  ["aa"]=>
  string(3) "80"
  ["ad"]=>
  NULL
  ["da"]=>
  string(4) "143.9"
  ["dr"]=>
  NULL
  ["cors"]=>
  NULL
}

I want to use IS NULL condition when a value is null in the array else I have to check equal to condition if not empty. I have tried something like below.

$po =  $option['po'] != NULL ? $option['po'] : NULL ;
$ca =  $option['ca'] != NULL ? $option['ca'] : NULL ;
$cl =  $option['cl'] != NULL ? $option['cl'] : NULL ;
$aa = $option['aa'] != NULL ? $option['aa'] : NULL ;
$ad = $option['ad'] != NULL ? $option['ad'] : NULL ;
$da = $option['da'] != NULL ? $option['da'] : NULL ;
$dr = $option['dr'] != NULL ? $option['dr'] : NULL ;
$cors = $option['cors'] != NULL ? $option['cors'] : NULL ;

The above code is not assigning value to a variable if option index is null I also tried is_null.

and SQL like below.

$sql = "SELECT abc FROM `abc` WHERE `po` = $po AND `ca` = $ca AND `cl` = $cl AND `aa` = $aa AND `ad` = $ad AND `da` = $da AND `dr` = $dr AND `dr` = '$dr' AND `cors` = '$cors' `sku` = '$sku' ";

I have to check IS NULL in SQL when value is null else I have to use equal to

Please share more details. Also, be warned that you should never use string concatenation to build SQL queries - they are vulnerable to SQL injections. Better use prepared statement — Nico Haase, May 29 '21 at 19:54
I'm not aware of string concatenation SQL and prepared statements. could you please share any links to learn more? — ramesh, May 29 '21 at 19:58
About prepared statements, if you use PDO: https://www.php.net/manual/en/pdo.prepared-statements.php and if you use MySQLi: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — M. Eriksson, May 29 '21 at 20:00
thanks. I don't have additional details. I hope I have explained clearly — ramesh, May 29 '21 at 20:03
_Side note:_ For all those variable assignments, you can use the [null coalescing operator](https://www.tutorialspoint.com/php7/php7_coalescing_operator.htm) instead: `$po = $option['po'] ?? NULL;` — M. Eriksson, May 29 '21 at 20:03
I'm not fluient in PHP, but isn't `$po = $option['po'] != NULL ? $option['po'] : NULL ;` exactly the same as `$po = $option['po']`? Why the more complicated expression? What are you trying to achieve with this? — Thorsten Kettner, May 29 '21 at 22:00
@ThorstenKettner in PHP there is type juggling so `!= null` effectively checks if something is _truthy_. https://www.php.net/manual/en/types.comparisons.php — mickmackusa, May 30 '21 at 03:49

score 1 · Answer 1 · answered May 29 '21 at 21:46

As you iterate your options variable (assuming you have already validated all of the keys using a whitelist of table column names), conditionally write a static IS NULL expression into the WHERE clause or a bound parameter with a non-null value.

// unconditionally include sku
$where[] = "sku = ?";
$params = ['s', $sku];
foreach ($option as $key => $value) {
    if ($value === null) {
        $where[] = "$key IS NULL";
    } else {
        $where[] = "$key = ?";
        $params[0] .= 's';
        $params[] = $value;
    }
}

$sql = "SELECT column_with_different_name_from_table
        FROM abc
        WHERE " . implode(" AND " , $where);
$stmt = $conn->prepare($sql);
$stmt->bind_param(...$params);
$stmt->execute();
$result = $stmt->get_result();

foreach ($result as $row) {
    echo $row['column_with_different_name_from_table'] . "<br>";
}

Kinglish · Answer 2 · 2021-05-29T20:26:31.993

You can use NULL in prepared statements directly. Heres a dynamic way to build your query

// put all your variables in an array
$vars = array('po', 'ca', 'ab') ;

// iterate through and set up your key/value pairs
foreach ($vars as $var) {
  $val = (isset($option[$var]) && !is_null($option[$var])) ? $option[$var] : NULL;
  $q[] = " $var = ? ";
  $v[] = $val ;     
}

// set up the prepared statement
$query = "SELECT abc FROM `abc` WHERE " . implode(" AND " , $q) ;
$stmt = $mysqli->prepare($query);
$types = str_repeat("s", count($q)) ;
$stmt->bind_param($types, ...$v);
$stmt->execute();

Thorsten Kettner · Answer 3 · 2021-05-30T07:46:05.330

-1

If you merely want optinal criteria, you can handle this in SQL by asking "is the parameter set, then use it, else don't".

$sql = "SELECT abc FROM abc WHERE (po = $po OR $po IS NULL) AND (ca = $ca OR $ca IS NULL) ...";

which can also be written as

$sql = "SELECT abc FROM abc WHERE po <=> COALESCE($po, po) AND ca <=> COALESCE($ca, ca) ...";

in MySQL (thus having each parameter only once in the query instead of having to use them twice).

UPDATE: I have focused on the SQL in above answer. It has been mentioned that you should not build the query string by concatenation, but use prepared statements and bind the variables. This is correct. I am not a PHP programmer, but it seems the difference in above assignments is mainly to replace the $ with a :. Something like this:

$sql = "SELECT abc FROM abc WHERE po <=> COALESCE(:po, po) AND ca <=> COALESCE(:ca, ca) ...";
$query = $pdo->prepare($sql);
$query->bindParam(":po", $option["po"]);
$query->bindParam(":ca", $option["ca"]);
...
$query->execute();

edited May 30 '21 at 07:46

answered May 29 '21 at 22:05

Thorsten Kettner

89,309
7
49
73

1

This is not satisfying the modern/professional standard. Variables should not be injected into the sql string, but bound to placeholders in a prepared statement. I did not expect to see this low-quality advice from a high-rep contributor. This answer is also missing its education explanation. – mickmackusa May 29 '21 at 23:29
1

More to the point, these variable injections will result in a rendered sql string that will have syntax errors. When you write `null` values where you have them, the sql will just put a zero-length string there. – mickmackusa May 30 '21 at 05:25
@mickmackusa: You are right of course. I just focused on the SQL, but of course one should not build the query string by concatenation, but use prepared statements instead As to the lines I am showing that means replacing the `$` with a `:`. I've updated my answer accordingly. Thanks for pointing this out. – Thorsten Kettner May 30 '21 at 07:35
1

For anyone not familiar with mysql's null-safe equals spaceship operator (not to be confused with php's three-way comparison spaceship operator): [What is this operator <=> in MySQL?](https://stackoverflow.com/q/21927117/2943403) – mickmackusa May 30 '21 at 07:46
While I appreciate that you have modernized your answer, I believe your answer does not maintain the required logic. If a given option value is `null`, then it should ONLY match a row where that null value exists in that specific column. In other words, your answer will match `col = col` when the passed in value is null -- this isn't right. Am I clear in my feedback? – mickmackusa May 30 '21 at 07:51
@mickmackusa: Ah, you may be right. I thought the OP wants optinal parameters, a common problem with SQL queries, where criteria must only be applied when given. Maybe they want to find null values when null is given instead. That would be `po IS NOT DISTINCT FROM $po` in standard SQL or just `po <=> $po` in MySQL – Thorsten Kettner May 31 '21 at 06:54

MySQL check if null if value is empty using php

3 Answers3