I have a form with a text field (<input name="a1" type="text" size="5">). If a user types "0", I want to insert "0" into my mysql db. If a user leaves the field blank, I want to insert NULL into the db.

However, my code keeps inserting "0" into my db even when the field is left blank. (I echoed out the variable and it's empty - so I don't know why PHP doesn't treat it as NULL).

To check what was happening, I wrote:

if (is_null($a1))
{
echo "a1 is NULL";
}
else echo "a1 is NOT NULL";

As I say, when I echoed out $a1 to double-check, it's empty but the above code keeps saying "a1 is NOT NULL". What am I doing wrong? Any pointers would be appreciated.

Well, there's two things you need to realize:

1) NULL isn't a string value, it's rather the ABSENCE of a value or type, much like the old "black isn't a color, it's the absence of a color" idea.

2) HTML form fields, when submitted, create STRING variables in PHP.

If the user doesn't fill in the blank, the value is the empty string, that is: "". The data type of this is still a string, it's just empty.

You need to test for an empty string, not "is_null".


Take care,

Nik
http://www.bigaction.org/

Also to add to Nik's comments in mysql a null value passed to an integer column will always result in that field's value being made zero. To avoid this you should use a different data type.
I haven't gone through mysql documentation to see if there is a way to get rid of the default zero on an integer field without changing data types but you could check.

So you see it is really mysql that makes that value zero and not PHP.

Also zero, null & boolean false are all synonymous in programming. In testing for value with the empty construct, zero is the same thing as null or boolean false.
http://www.php.net/empty

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

Technically that's not true. If you actually insert a NULL into an integer column, one of two things will happen -- if the column supports NULL values, then you'll insert the NULL value, not a zero. If the column does NOT support NULL values, then you'll get an error message that the value being inserted into that column cannot be NULL.

The behavior you describe doesn't happen when you insert NULLs, and it doesn't always insert zero... Zero is the default default value. Yes, I meant to type "default" twice. Every column in the database can have a default value that gets inserted should that column NOT be listed in the insert query.

For integer columns, if you don't specify a default value, then the value will be NULL if the column supports NULLs, or zero if it doesn't.


To recap this whole post in SQL:

CREATE TABLE nulltest
(
  nullint int(11) default NULL,
  regint int(11) NOT NULL default '0',
);

INSERT INTO nulltest (nullint, regint) VALUES (NULL, 0); // ok
INSERT INTO nulltest (nullint, regint) VALUES (0, NULL); // #1048 - Column 'regint' cannot be null

INSERT INTO nulltest (nullint) VALUES (NULL); // ok, regint gets zero by default.
INSERT INTO nulltest (regint) VALUES (0); // ok, nullint gets NULL by default.



Take care,

Nik
http://www.bigaction.org/

Well it would be true if you set up your integer field so that it couldn't be null and were inserting an empty string.

Basically what I was trying to say is when using an integer field and inserting an empty string (regardless of null settings) it will always replace that empty string with zero (at least it does on my test scripts with a common int config.) to avoid this an unquoted null constant must be used in conjunction with the allow NULL setting applied to the table. Which I understand now that an empty string and a null aren't the same in this context.

Every integer field that I have is set up that way (NOT NULL) and so that's the experience I was drawing on. If the user did not enter input it came back zero which was the result of inserting an empty string and not a null value as I contended before.

I should have taken the time to research my response a little more, but hey what can I say I'm lazy : ). Thanks for pointing me in the right direction!

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

Yeah, it's that clarification that's important -- there is a huge difference between inserting a NULL value, inserting an empty value (''), and inserting NO value (not including that column in the insert list).


Take care,

Nik
http://www.bigaction.org/

Hi,

Than how can I insert NULL value into mysql int field from html textbox?

My column structure like...

ilocat int(6) default NULL,
idate date default NULL,

Regards,

Niraj

Remove the 'NULL' parameter from the mysql field.

Regards,
Rich

--
[http://www.smilingsouls.net]
[http://pear.php.net/Mail_IMAP] A PHP/C-Client/PEAR solution for webmail

You can see in this post, the 'NOT NULL' option, which allows a NULL value to exist.

Regards,
Rich

--
[http://www.smilingsouls.net]
[http://pear.php.net/Mail_IMAP] A PHP/C-Client/PEAR solution for webmail