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

Question

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)

Volkan · Accepted Answer · 2013-03-13 08:57:37Z

3

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.

answered Mar 13, 2013 at 8:57

Volkan

2,2101 gold badge15 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related