inserting NULL into column via variable

[witt]

How would you insert NULL into a column?

[poirot]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]table[/color] (field) VALUES (NULL) [!--sql2--][/div][!--sql3--]
[a href=\"http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html\" target=\"_blank\"]MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values[/a]

[witt]

[!--quoteo(post=379907:date=Jun 4 2006, 10:25 AM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 4 2006, 10:25 AM) [snapback]379907[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]table[/color] (field) VALUES (NULL) [!--sql2--][/div][!--sql3--]
[a href=\"http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html\" target=\"_blank\"]MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values[/a]
[/quote]

You should have read the title. What I want to do is insert NULL into the column via a variable. A query that inserts a variable into the column is run, and if that variable happens to be NULL, which it might not be, I want it to be inserted as NULL.

[wildteen88]

Something like this:
[code](($foo == null) ? $bar = null : $bar = $foo);[/code]
Basically that checks whether the $foo variable is null, if it is it'll set $bar as null, otherwise if $foo is not null it'll set $bar as the value of $foo.

If it isn't could you explain what your are trying to do in much more detail and if you have any PHP code then paste that here too which will be able to aid in a better answer rather than us having to guess.

[witt]

The problem is with the insertion of NULL, not with assigning it.

if (empty($_POST['name'])) {
$name = NULL;
} else {
$name = $_POST['name'];
}

$query = "UPDATE users SET name='$name'";

The problem is that a blank value is inserted instead of NULL.

[wildteen88]

Then do this:
[code] $name = 'NULL';[/code]
If you define a variable as NULL the value of the variable is set to nothing. If you want the variable to store the actuall value NULL (as-is) then surround it in quotes. Therefore when it goes into your query it should place NULL into your query if the $_POST['name'] is empty.

[poirot]

Exactly. You must use the quotes because you are not putting the NULL type, but rather a string named 'NULL'.

The SQL query can't "understand" PHP's NULL. Basically something like this will do:

[code]$name = (empty($_POST['name'])) ? 'NULL' : "'{$_POST['name']}'";
$query = "UPDATE users SET name=$name";[/code]

[witt]

If you set the variable to 'NULL' and insert that into the database, aren't you just inserting it as a string?

[poirot]

No. If you use my code, it generates:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] users SET name[color=orange]=[/color]NULL
#or
[span style=\'color:blue;font-weight:bold\']UPDATE[/span] users SET name[color=orange]=[/color][color=red]'something'[/color] [!--sql2--][/div][!--sql3--]