Page 1 of 1

Null but not Null???

Posted: Fri Nov 18, 2005 10:27 am
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

Posted: Fri Nov 18, 2005 10:30 am
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 :)

Posted: Fri Nov 18, 2005 10:54 am
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.

Posted: Fri Nov 18, 2005 10:59 am
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';
}
?>

Posted: Fri Nov 18, 2005 11:10 am
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. :)

Posted: Fri Nov 18, 2005 11:13 am
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);

Posted: Fri Nov 18, 2005 11:17 am
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);

Posted: Fri Nov 18, 2005 11:50 am
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);