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