inserting nulls

[manrower]

Ive got a form with a long list of things to insert into my sql db, but I need to be able to let the user keep a value NULL. The db is set to accept NULL values, so that part is okay. The problem is that a null causes there to be an empty space in the INSERT query's value list (Ex/ INSERT...VALUES($val1, , $val3), where $val2 was the NULL value), making 2 comma's be right next to eachother and hence be an SQL syntax error. I feel like there should be something simple to get around this problem. What do you guys do?

Aaron

[bad76]

HI,
I think you have to do
VALUES($val1,'', $val3)
for text field (char, varchar, ....)
or
VALUES($val1,0, $val3)
for numeric field (tinyint, int, float...)

Even you can use the complete syntax:

INSERT INTO tabl_name (a_field_name1, a_field_name2, a_field_name3, ...) VALUES (a_field_value1, a_field_value2, a_field_value3...)

Where you can skip null fields.

see you

[Sxooter]

The proper syntax is to use NULL with no quotes.

insert into table test (field1, field2, field3)
values ('abc',NULL,'def');

Note that '' (a null string) is NOT a NULL. a null string is simply a string with no characters ending in \0. A SQL NULL means "we don't know if there is a value for this or not."

This means that:

$a='';
$b=''
print $a==$b will be true.

but, in SQL:

select coalesce(NULL=NULL,true)

will return true, which means the result of comparing a null with a null is null. In english:

Q: Is this unknown value equal to this unknown value?

A: We don't know.

If your database thinks '' equals null or that null can equal null, then it's broken. By the way, this is how Oracle works. Yeah, it's broken.

[manrower]

Thanks you two, but Im still not getting it to work.
it doesnt work, as was first suggested, to do this:
VALUE($val1, '', $val2)

it does work, as was secondly suggested, to do this:
VALUE($val1, NULL, $val2)

But I need it to work when I do this:
VALUE($val1, $val2, $val3)
Where $val2 is an empty string that I need it to interpret as NULL.

I tried :
if($val2 == '') {
$val2 = NULL;
}
above the INSERT statement, but that doesnt seem to work.

What would work?

[helz]

try:

if (empty($val2)) {
   $val2 = NULL;
}

[Sxooter]

Make sure that after you do the

if ($val2='') $val2="NULL";

that you don't stick it inside ' marks in the insert like this:

WRONG:
$query = "insert into table ('$val1','$val2','$val3');

RIGHT:
$query = "insert into table ('$val1',$val2,'$val3');

[manrower]

Thanks! You all contributed helpful bits.
This ended up working:

if($val1 == '') {$val1 = NULL;}... etc. for all val's

$SQLselect= "INSERT INTO $table ( val1, val2, val3 )
VALUES('$val1', '$val2', '$val3');";

Any of the values could be NULL and it would be reflected as such in the database. The querkiness of this was mind boggling. '$val1'... did not work initially because I had '.' between the variable names and VALUES. I also had to put double quotes around the statement... though it originally worked with single quotes. '.$val1.'==NULL always generated an error, though it worked when none of the values were NULL.

Thanks everyone

[ahundiak]

And, as Sxooter pointed out, make sure you do:
$val2 = 'NULL';
and not
$val2 = NULL;

NULL is also a reserved php keyword and not directly related to a sql NULL.

[Sxooter]

Originally posted by manrower
Thanks! You all contributed helpful bits.
This ended up working:

if($val1 == '') {$val1 = NULL;}... etc. for all val's

$SQLselect= "INSERT INTO $table ( val1, val2, val3 )
VALUES('$val1', '$val2', '$val3');";

Note that right now, you're inserting the STRING 'NULL' into your database, not NULL.

I.e. you need to be able to get rid of the ' marks around $valx if you want to insert a NULL.

I.e. if you do

select * from table where val2 IS NULL

Then the values you've inserted 'NULL' into aren't going to show up, because they're not NULL, the place holder for unknown, but 'NULL' a string. At that point you may as well insert 'NOT A VALUE' or anything else.

[manrower]

But if I do this:
put quotes around NULL:
if($val1 == '') {$val1 = 'NULL';}... etc. for all val's

and remove quotes around $valx:
$SQLselect= "INSERT INTO $table ( val1, val2, val3 )
VALUES($val1, $val2, $val3);";

I get this error:
You have an error in your SQL syntax near ' NULL, NULL, NULL )'

And if I alternatively do this:
no quotes around NULL:
if($val1 == '') {$val1 = 'NULL';}... etc. for all val's

and still remove quotes around $valx:
$SQLselect= "INSERT INTO $table ( val1, val2, val3 )
VALUES($val1, $val2, $val3);";

Then I get this error:
You have an error in your SQL syntax near ' , , , , , , , , )'

This way is the only way that doesnt get an error:
if($val1 == '') {$val1 = NULL;}... etc. for all val's
$SQLselect= "INSERT INTO $table ( val1, val2, val3 )
VALUES('$val1', '$val2', '$val3');";

I think its working because when I look up the database in phpMyAdmin it says NULL in the table and when I display the database table through my website it is an empty space.

[Sxooter]

OK. one more time. Slowly...

NULL is a null value (unknown / unknowable / undefined, etc...)
'NULL' = a string. You could change it to 'NALL' and it would function exactly the same. It would be a known value called 'NALL' instead of 'NULL.

Comparisons:

select NULL IS NULL;
TRUE
select NULL = NULL;
NULL
select 'NULL' = 'NULL';
TRUE
select 'NULL' = NULL;
NULL
select NULL = 'NULL';
NULL

If you run an actual check against your data as it is stored, you'll see I'm right.

Now, how to store it? It's not as simple as just do or don't use single quotes on every field. You have to check and if you're doing NULLs NOT use the ticks, and if you doing data, then use the tick marks.

Yes it's a pain.

No, there's no other simple method.

If you quote something, it is, by definition, a string or maybe a number, but definitely a known value.

The keywords TRUE, FALSE, DEFAULT and NULL as defined by the SQL spec aren't quoted. If you quote them, they go from being keywords to being strings, and that's not the same thing.

[helz]

if you have a form and the user doesnt fill in some of the fields, but the fields get added into the database anyway but as "" or nothing, is that the same as NULL ?

[Sxooter]

OK. You do the quoting when building the values, not when building the query.

<?php
$val['total'] = 0;
$val['title'] = '';  # <- this will become a NULL
$val['comment'] = 'stanley';
$fields = array( "total", "title", "comment");
$table = "comments";
$query = "insert into $table (";
$query.= implode(",",array_values($fields));
$query.= ") values (";
$count = count($vals);
foreach($fields as $f){
        if ($val[$f] == '' && is_string($val[$f])){
                $val[$f] = 'NULL';
        } else if (is_string($val[$f])){
                $val[$f] ="'".$val[$f]."'";
        } else {
                # Do nothing, it's a number...)
        }
}
$query.= implode (",",array_values($val));
$query.=")";
print "\n\n";
print $query;
print "\n\n";
?>

[Sxooter]

Originally posted by helz
if you have a form and the user doesnt fill in some of the fields, but the fields get added into the database anyway but as "" or nothing, is that the same as NULL ?

It's not supposed to be, but some databases (Oracle in particular) have a default setting that converts and empty string into NULL.

Most databases, like Postgresql, interbase / firebird, db2, etc... obey the SQL spec that says that if it's quoted, it's not NULL, even if it's empty.

NULL has a very real meaning, and it's not the same as an empty string.

Interpeting one as the other is a logical error that can lead to subtle data issues later in life.

[mrsocks]

Originally posted by Sxooter
OK. You do the quoting when building the values, not when building the query.

<?php
$val['total'] = 0;
$val['title'] = '';  # <- this will become a NULL
$val['comment'] = 'stanley';
$fields = array( "total", "title", "comment");
$table = "comments";
$query = "insert into $table (";
$query.= implode(",",array_values($fields));
$query.= ") values (";
$count = count($vals);
foreach($fields as $f){
        if ($val[$f] == '' && is_string($val[$f])){
                $val[$f] = 'NULL';
        } else if (is_string($val[$f])){
                $val[$f] ="'".$val[$f]."'";
        } else {
                # Do nothing, it's a number...)
        }
}
$query.= implode (",",array_values($val));
$query.=")";
print "\n\n";
print $query;
print "\n\n";
?>

[/B]

where did you get this from?
i am trying this out and it is not inserting the values. as of right now, the first value is not being enclosed in single quotes. I have tried the sql that this code generates with and with out the single quotes and it only works with the single quotes. at the end of this code i am adding:
$result = mysql_query($query); to execute the query , but not getting and inserted values.

what am i missing?\

thanks

[Sxooter]

Where did you get this from?

I wrote it.

Here's a version that quotes EVERYTHING but the NULLs:

<?php
$val['total'] = 0;
$val['title'] = '';  # <- this will become a NULL
$val['comment'] = 'stanley';
$fields = array( "total", "title", "comment");
$table = "comments";
$query = "insert into $table (";
$query.= implode(",",array_values($fields));
$query.= ") values (";
$count = count($vals);
foreach($fields as $f){
        if ($val[$f] == '' && is_string($val[$f])){
                $val[$f] = 'NULL';
        } else{
                $val[$f] ="'".$val[$f]."'";
        } 
}
$query.= implode (",",array_values($val));
$query.=")";
print "\n\n";
print $query;
print "\n\n";
?>

Notice I just took a chainsaw to it.