CODEWITHSUNDEEP
phpnullisset
John Doe Smith
John Doe Smith

Reputation: 1643

php isset and is_null: problems with notset values and null-values in php condition

This is for preparing a string for a mysql-query. The problem is about the NULL. I want ONLY the 'd'-column to be NULL (if set as null in data-array). the problem is that 'isset' is false if null and 'is_null' is also true if not set..

So how can I fix that?

I have the following in php:

<?
$data = array(
    array('id' => 1, 'c' => '', 'd' => NULL, 'e' => '', 'f' => '', 'i' => 'ciao', 'j' => '', 'k' => ''),
    array('id' => 2, 'b' => '', 'c' => '', 'd' => NULL, 'e' => '', 'f' => 'hello', 'g' => '', 'h' => '', 'i' => ''),
);

$col = array('id', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k');
$insert = $update = array();

foreach (array_values($data) as $key => $val){
    $val_stack = array();
    $insert[$key] = "(";

    foreach ($col as $c){
        if (isset($val[$c]) && is_null($val[$c])) $val_stack[] = 'NULL';
        else if (isset($val[$c])) $val_stack[] = "'".$val[$c]."'";
        else $val_stack[] = "''";
    }

    $insert[$key] .= implode(', ', $val_stack).")";
}

foreach (array_diff($col, array('id')) as $v){
    $update[] = $v == 'd' ? "$v=COALESCE(VALUES($v), $v)" : "$v=VALUES($v)";
}

echo "INSERT INTO table (".implode(', ', $col).") VALUES ".implode(', ', $insert)." ON DUPLICATE KEY UPDATE ".implode(', ', $update);
?>

THIS OUTPUTS:

INSERT INTO table (id, b, c, d, e, f, g, h, i, j, k) VALUES ('1', '', '', '', '', '', '', '', 'ciao', '', ''), ('2', '', '', '', '', 'hello', '', '', '', '', '') ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=COALESCE(VALUES(d), d), e=VALUES(e), f=VALUES(f), g=VALUES(g), h=VALUES(h), i=VALUES(i), j=VALUES(j), k=VALUES(k)

If I comment the "isset" in the inner "foreach" (if (/* isset($val[$c]) && */is_null($val[$c]))) it outputs:

INSERT INTO table (id, b, c, d, e, f, g, h, i, j, k) VALUES ('1', NULL, '', NULL, '', '', NULL, NULL, 'ciao', '', ''), ('2', '', '', NULL, '', 'hello', '', '', '', NULL, NULL) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=COALESCE(VALUES(d), d), e=VALUES(e), f=VALUES(f), g=VALUES(g), h=VALUES(h), i=VALUES(i), j=VALUES(j), k=VALUES(k)

THE OUTPUT I WANT:

INSERT INTO table (id, b, c, d, e, f, g, h, i, j, k) VALUES ('1', '', '', NULL, '', '', '', '', 'ciao', '', ''), ('2', '', '', NULL, '', 'hello', '', '', '', '', '') ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=COALESCE(VALUES(d), d), e=VALUES(e), f=VALUES(f), g=VALUES(g), h=VALUES(h), i=VALUES(i), j=VALUES(j), k=VALUES(k)

Upvotes: 1

Views: 225

Answers (1)

Volkan
Volkan

Reputation: 2210

You can use array_key_exists($c,$val) instead of isset($val[$c]). It works very consistent. I had the same problem solved that way.

Upvotes: 3

Related Questions