Null but not Null???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Null but not Null???

Post by seodevhead »

In one of my scripts, for some reason one of my values that is NULL when inserted into mysql is not showing as NULL in the table. It is rather coming up with a value of '0'. I have code similar to the following:

Code: Select all

$x = NULL;

$query = "INSERT INTO table_name (x_col) VALUES ('$x')";
$result = @mysql_query($query);
When I look at the table in mysql, the value for this insert is '0', not the usual blank/null field. Any ideas why this is happening? Thanks.

P.S. - x_col is DEFAULT NULL
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

To insert NULL into the DB, you need to enter the SQL syntax for NULL, which is (string) 'NULL'

Code: Select all

$x = 'NULL';

$query = "INSERT INTO table_name (x_col) VALUES ($x)";
$result = mysql_query($query);
Also, don't use the suppress operator ('@') when developing, errors are a devlopers best friend. Handle them appropraitely, don't hide them :)
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Hey Jenk,

I want $x to not be a string saying 'NULL', rather just set it to NULL using:

$x = NULL;

The problem is, with my current INSERT query, I have to use VALUES ('$x') because $x will be a string if the conditional that sets $x does not make it NULL.

I don't think I can use VALUES ($x) because of this reason.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Then you must change the way your code works :)

Either switch so that you can use 0 as an identifier for NULL, e.g.:

Code: Select all

<?php
foreach ($row as $col => $val) {
    if ($val == '0') {
        $replacementRow[$col] = NULL;
    } else {
        $replacementRow[$col] = $val;
    }
}
?>
Or change the method in which your SQL INSERT statement is constructed.
I'd personally go for the latter and change the process of statement construction. :)

A quick and nasty change:

Code: Select all

<?php
if ($x !== NULL) {
    $x = '\'' . $x . '\'';
} else {
    $x = 'NULL';
}
?>
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Thanks for the help, but I am a bit confused and I'll tell you why.

I have numerous other scripts that have code like the following:

ie. form handling script (the user leaves last name field blank)..

Code: Select all

if (!empty($_POST['last_name']))
$ln = $_POST['last_name'];
else
$ln = NULL;

$query = "INSERT INTO table_name (last_name) VALUES ('$ln')"; 
$result = @mysql_query($query);
With the code above, it has successfully inserted NULL into the table column if the $last_name variable is indeed NULL. For instance, when looking at the table in PHPmyAdmin, if all other columns were inserted with string values except the last_name column which was inserted a NULL value, the last_name column would be blank.

In the instance in my first post, instead of coming up blank as a NULL value, there is a '0' being displayed and I'm not sure why. This script is no different than the one I am currently having difficulty with. Any ideas? Thanks. :)
Last edited by seodevhead on Fri Nov 18, 2005 11:16 am, edited 1 time in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

You have an error in your example, $ln is not anywhere in the SQL statement.

If you run (try this manually on the MySQL command line..) a query with INSERT VALUES ('NULL'); you will have that column contain a string with the value of "NULL" in it.

The SQL syntax for inserting a NULL field is INSERT VALUES (NULL);
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Hey Jenk,

Sorry about that... $ln was supposed to be the value in the INSERT. The correct code I use is:

Code: Select all

if (!empty($_POST['last_name'])) 
$ln = $_POST['last_name']; 
else 
$ln = NULL; 

$query = "INSERT INTO table_name (last_name) VALUES ('$ln')"; 
$result = @mysql_query($query);
User avatar
J_Iceman05
Forum Commoner
Posts: 72
Joined: Wed Aug 03, 2005 10:52 am
Location: Las Vegas, NV

Post by J_Iceman05 »

Jenk wrote:To insert NULL into the DB, you need to enter the SQL syntax for NULL, which is (string) 'NULL'

Code: Select all

$x = 'NULL';

$query = "INSERT INTO table_name (x_col) VALUES ($x)";
$result = mysql_query($query);
Also, don't use the suppress operator ('@') when developing, errors are a devlopers best friend. Handle them appropraitely, don't hide them :)
I think there was a miscommunication with some of this earlier... when you say $x = NULL, you are say that the variable is null, not that you are trying to make the field value = NULL. saying that $x = 'NULL' actually makes the insert query equal

Code: Select all

$query = "INSERT INTO table_name (x_col) VALUES (NULL)";
remember... when you type the query, you need to act as if the variable is being echoed. if you set the variable to equal null, then it wont echo anything (it isn't set) you want it to echo NULL, so that is why you have the quotes around it.


if you wanted to put in the string of "NULL" into the database then you would need to put...

Code: Select all

$x = 'NULL';

$query = "INSERT INTO table_name (x_col) VALUES ('$x')";
// written out is..
// $query = "INSERT INTO table_name (x_col) VALUES ('NULL')";

$result = mysql_query($query);
and for interchangability...
seodevhead wrote:Hey Jenk,

Sorry about that... $ln was upposed to be the value in the INSERT. The correct code I use is:

Code: Select all

if (!empty($_POST['last_name']))
$ln = $_POST['last_name'];
else
$ln = NULL;

$query = "INSERT INTO table_name (last_name) VALUES ('$ln')";
$result = @mysql_query($query);
I think this should be...

Code: Select all

if (!empty($_POST['last_name']))
$ln = "'".$_POST['last_name']."'";  // setting $ln to equal the value of $_POST['last_name'] with single quotes (') around it.  ('value')
else
$ln = 'NULL';

$query = "INSERT INTO table_name (last_name) VALUES ($ln)";  // remove the single quotes around $ln,  otherwise the $ln having a value of NULL would be put into the database as the string "NULL"  and not having a NULL value itself.
         // plus, having the single quotes around the POST variable allows you to not require an if statement on having the single quotes in the query as well.
$result = mysql_query($query);
Post Reply