Jump to content

Archived

This topic is now archived and is closed to further replies.

witt

inserting NULL into column via variable

Recommended Posts

[!--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]

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
If you set the variable to 'NULL' and insert that into the database, aren't you just inserting it as a string?

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.