Page 1 of 1

DEFAULT NULL for empty values

Posted: Mon Dec 01, 2003 1:13 pm
by kendall
Hello,

I have a table in which its fields are set to DEFAULT NULL

my question is

if on a query i INSERT....VALUES('','','','');

wouldnt my fields values be set to NULL as a result of the values being empty? and not be empty?

I have been advised about having null values but is it necessary ot ensure that an entry has all values?

Kendall

Posted: Mon Dec 01, 2003 1:19 pm
by microthick
I believe your fields would be set to empty strings and not null.

But, I'm still sorta confused.

Check the first point here for some even more confusing info:
http://sql-info.de/mysql/gotchas.html

Posted: Wed Dec 03, 2003 11:55 am
by JAM
Null values in sql is NULL while ''/"" is an empty string.

In your own example:

Code: Select all

INSERT....VALUES('','','','');  -- inserting empty strings
INSERT....VALUES(NULL,NULL,NULL,NULL);  -- inserting NULL
and to recreate that:

Code: Select all

CREATE TABLE nulltest (
  isitnull char(1) default NULL
) TYPE=MyISAM;

INSERT INTO nulltest VALUES ('0');
INSERT INTO nulltest VALUES ('');
INSERT INTO nulltest VALUES (NULL);

SELECT * FROM nulltest WHERE isitnull IS NULL
...will only return the last inserted row, so clearly NULL is to be used if that is what you really intend to.

Posted: Wed Dec 03, 2003 2:06 pm
by JayBird
i've had this problem to.

What is the best way to insert NULL if no value was entered in say a form?

Mark

Posted: Wed Dec 03, 2003 2:36 pm
by JAM
Well, a small verification by using strlen, isset, empty or similiar should be in order.

Code: Select all

if (empty($_POST['foo'])) { // yes, possible 0 problem with empty()...
 $inserting = NULL;
} else {
 $inserting = $_POST['foo'];
}
$swl = "insert into table (bar) values('$inserting')"; // or whatever