DEFAULT NULL for empty values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

DEFAULT NULL for empty values

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
Post Reply