inserting NULL into db

expert_21

hi!

say for instance i have a variable:

$a=$_POST[manufacturer];
...
INSERT INTO table VALUES($a);

how do i make it so that if no value is added to manufacturer, the entry to the db will be NULL value? thank you.

rgermain

There is several ways to do this.

<?

if($a == "")
{
$query = "INSERT INTO my_tbl (manufacturer) VALUES ('NULL');
$result = mysql_query($query) or die("Error is: " . mysql_error());
}
else
{
$query = "INSERT INTO my_tbl (manufacturer) VALUES ('$a);
$result = mysql_query($query) or die("Error is: " . mysql_error());
}
?>

laserlight

well, what do you mean by "no value is added to manufacturer"?

If $_POST['manufacturer'] (note the quotes) is empty, then
INSERT INTO table VALUES('$a')
(again note the quotes) should suffice.

swr

If you want to insert an actual null value instead of the string 'NULL' then you need to drop the quotes.

insert into table (column) values ('NULL')

will insert a string, while

insert into table (column) values (NULL)

will insert an proper null value. Which is not to be confused with this

insert into table (column) values ('')

which inserts a zero-length string. PHP often lets you use nulls and zero-length strings interchangably, but really they are completely different things.

The following code handles all three values correctly:

if (isset($a)) {
    $sql_a = "'".mysql_escape_string($a)."'";
}
else {
    $sql_a = "NULL";
}
mysql_query("insert into table (column) values ($sql_a)") or die(mysql_error());

laserlight

which inserts a zero-length string

That's a good point.
On the other hand, I dont think we're dealing with strings terminated by '\0', so shouldnt an empty string be equivalent to a null value?
This would also be in view that null can be interpreted as either data that is unknown, or data that has yet to be obtained.

swr

A "null" is the lack of any value. A "null byte" is a byte of value zero.

"" is a string of zero length, but still a string. null is not a string, and doesn't have length of 0 because it doesn't have a length at all. null is the lack of any value, string or otherwise.

Use error_reporting(E_ALL) and you will quickly learn the difference.

laserlight

"" is a string of zero length, but still a string. null is not a string, and doesn't have length of 0 because it doesn't have a length at all. null is the lack of any value, string or otherwise.

Quite obviously I already know that, within PHP.
The problem comes within MySQL.

Nonetheless the mysql manual does clear it up to be the same:
http://www.mysql.com/doc/en/Problems_with_NULL.html

expert_21

thanks for the replies! i got it right thanks to you guys.

i am just not sure about the difference between this:

$sql_a = "'".mysql_escape_string($a)."'";

and this:

$sql_a = "mysql_escape_string($a)";

what is the '". doing there? i am really not sure about the syntax. For instance, you could have $POST[manufacturer] and $POST['manufacturer'] and both would work similar. 🙁

swr

Originally posted by expert_21
i am just not sure about the difference between this:

$sql_a = "'".mysql_escape_string($a)."'";

and this:

$sql_a = "mysql_escape_string($a)";

what is the '". doing there? i am really not sure about the syntax.

Try them both, and echo $sql_a after each one. Often the best way to learn is through experimentation.

This page describes the string-related syntax:
http://www.php.net/manual/en/language.types.string.php

For instance, you could have $POST[manufacturer] and $POST['manufacturer'] and both would work similar. 🙁 [/B]

Use the second one. See this page, in the "Why is $foo[bar] wrong?" section:
http://www.php.net/manual/en/language.types.array.php