Page 1 of 1
MySQL - Null... yes or no?
Posted: Thu Jan 18, 2007 12:47 pm
by batfastad
Hi everyone
I'm fairly new to MySQL and have been messing around with simple databases for about a year now using phpMyAdmin etc.
But one thing that's always puzzled me is the behaviour of setting fields to allow Null values and setting a default.
At the moment what I usually do is have
all my fields set to not allow Null values - NOT NULL
Even if there are certain fields that I wish to remain empty.
When setting the field type as VARCHAR, phpMyAdmin sets the default value for that field as empty anyway.
Obviously with the INT type it sets the default as 0.
Is this the correct way of doing things?
For example I have a MySQL database containing names and addresses with the following fields...
Code: Select all
id
contact_name
company_name
address1
address2
address3
town_city
county_state
postcode_zip
country
... all fields are set to not accept Null values - NOT NULL.
But most records don't have the address2 or address3 fields filled in, they're just blank / empty.
Is this the best way?
Or in my PHP script should I be setting those fields as NULL rather than putting an empty value?
Thanks
Ben
Posted: Thu Jan 18, 2007 1:42 pm
by hrubos
follow my exprience(I have been learning PHP for fews month

) , so key you have to chose NOT NULL, and then [s]u[/s]
you can chose NULL or NOT NULL follow your work.
Example :
You have a table with these fields
id : NOT NULL
contact_name : NULL (if somtimes you insert into this fiedls, but sometime you don't need insert into it )
company_name
address1
address2
address3
town_city
county_state
postcode_zip
country
So I hope that you understand what I am doing
Enjoy it !!!
Posted: Sun Jan 28, 2007 2:45 pm
by califdon
You're probably trying to read too much into the NULL field property. Its principal purpose is simply to insure that critical data is not omitted, such as a primary key field. It can be used for other purposes as well, but my advice (and I've been doing this for over 20 years) is to use NOT NULL sparingly, only for fields where a lack of a value in the field would cause serious problems in the operation of the database. This will make your INSERT statements a lot simpler. Years of experiencing clients who state positively that "we will ALWAYS have a value in this field" and then, months later, come back moaning, "it won't let us enter a new record because we haven't yet assigned a value to xyz field!" have taught me to do this. On the other hand, that gives me the opportunity to charge them another consulting fee, so maybe it's not too bad an idea ...

Posted: Mon Jan 29, 2007 1:49 am
by jmut
califdon:
well you are problably right ... I don't have so much experience but the problem usually is that data comes as `null` not an empty string.
So it harder to use data after it is inserted. Yes inserts are easier but then you might have a little headache.....I guess that is why people would prefer to prefill all data...even if empty rather than insert null.
Me personally use null columns when column should be unique.
Posted: Wed Feb 21, 2007 12:25 pm
by batfastad
Hi everyone
Sorry for taking so long to reply. I didn't have this topic watched for replies.
Thanks for all the info so far.
At the moment all my fields in my database
do not allow NULL values - especially important fields like IDs etc.
I do all the data validation in PHP before the data gets saved to the database - PHP is where I check for values of required fields.
But where there's a field that doesn't need to have a value, I'm saving that in the database as just a blank 0-length string if there's nothing in that particular variable...
Code: Select all
UPDATE companies SET fax='$fax', tel='$tel' WHERE id ='$id'
Not as NULL.
Is that acceptable?
Or for my optional fields like address lines, tel, fax, email, website should I be getting PHP to save it as NULL if the user entered nothing?
Thanks
Ben
Posted: Thu Feb 22, 2007 1:58 am
by mikeq
I would say no it is not acceptable
with this table definition
Code: Select all
CREATE TABLE `table1` (
`field1` int(11) NOT NULL auto_increment,
`field2` varchar(20) default NULL,
`field3` varchar(20) default NULL,
PRIMARY KEY (`field1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
the default values for fields 2 and 3 are NULL
so this insert
Code: Select all
INSERT INTO table1 (field3)
VALUES
('blah')
will produce
1 NULL blah
So I would say dont insert an empty string, you would then have to do queries like
Code: Select all
SELECT *
FROM table1
WHERE field2 IS NULL OR field2 = '';
rather than just
Code: Select all
SELECT *
FROM table1
WHERE field2 IS NULL;
AS califdon says, don't make all your fields "not null" it is not good database design. If a field can store NULLS then let it do so.
Posted: Thu Mar 01, 2007 11:31 am
by batfastad
Ok cool.
So rather than having empty fields, always set them to NULL.
So if I'm doing the following insert statement...
Code: Select all
INSERT INTO materials VALUES ('', '$company_id', $time_now, '$user_name', '$user_initial', $material, $notes, $mag, $type, $where_who);
BTW that first field is an ID auto-increment field.
If say $mag or $type was empty, is there a way I can tell it to insert NULL rather than just an empty value?
Do I need to do something like this for each variable?
Or will this just put the word NULL into the field?
Code: Select all
if ( strlen($mag) < 1) {
$mag = 'NULL';
}
Rather than checking each variable is there a way I can just tell it to insert NULL if any of the fields are empty?
Thanks everyone for all your advice so far!
Ben
Posted: Thu Mar 01, 2007 11:34 am
by batfastad
Ok I've answered my own question...
http://uk.php.net/manual/en/language.ty ... ull.syntax
The following would be the code I need without the single-quotes round NULL...
Code: Select all
if ( strlen($mag) < 1) {
$mag = NULL;
}
But is there a way to have any empty fields set to NULL rather than just an empty string submitted from the form - in one go, rather than doing the above code for each variable?
Thanks
Ben
Posted: Thu Mar 01, 2007 1:35 pm
by mikeq
It would only default to NULL if you didnt attempt to set the field value.
That would mean dynamically producing the field list to insert to based on any '' empty strings
so
Code: Select all
INSERT INTO table1 (field1, field2, field3) values (.....
so based on what someone POSTS to your script the field1, field2 etc would be generated and exclude those fields where an empty string would be passed.
But this would still require checking and is probably more work than your own suggestion.
If you are using MySQL 5 then you can use triggers to do this kind of processing before inserts etc.
Posted: Thu Mar 01, 2007 2:35 pm
by batfastad
So if I did this on all my variables...
Code: Select all
if ( strlen($mag) < 1) {
$mag = NULL;
}
Then this SQL command...
Code: Select all
INSERT INTO materials VALUES ('', '$company_id', $time_now, '$user_name', '$user_initial', $material, $notes, $mag, $type, $where_who);
Would that set the data as NULL in the database?
I did think about dynamically creating the query.
But if there's no other obvous way, I guess this is the way to go!
Posted: Fri Mar 02, 2007 1:09 am
by jmut
It is time you try all this yourself. Would have saved hours by now.
Posted: Tue Mar 06, 2007 4:51 am
by batfastad
Thanks for all your help mikeq
I had it working but I needed to know what best practice might be.