Jump to content


Photo

inserting NULL into column via variable


  • Please log in to reply
8 replies to this topic

#1 witt

witt
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 04 June 2006 - 02:39 PM

How would you insert NULL into a column?


#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 04 June 2006 - 03:25 PM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] INTO table (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]
~ D Kuang

#3 witt

witt
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 04 June 2006 - 05:27 PM

[!--quoteo(post=379907:date=Jun 4 2006, 10:25 AM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 4 2006, 10:25 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] INTO table (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.

#4 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 04 June 2006 - 05:46 PM

Something like this:
(($foo == null) ? $bar = null : $bar = $foo);
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.

#5 witt

witt
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 04 June 2006 - 06:14 PM

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.


#6 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 04 June 2006 - 06:30 PM

Then do this:
$name = 'NULL';
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.

#7 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 04 June 2006 - 06:34 PM

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:

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

~ D Kuang

#8 witt

witt
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 04 June 2006 - 06:38 PM

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

#9 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 04 June 2006 - 06:41 PM

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=NULL
#or
[span style=\'color:blue;font-weight:bold\']UPDATE[/span] users SET name='something' [!--sql2--][/div][!--sql3--]

~ D Kuang




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users