NYCPHP Meetup

[Kristina D. H. Anderson]

>>But in the case of a form, wouldn't you be validating the input before
>>trying to insert the record? Sorry if I seem dense--I must be
>>misunderstanding something.

No, you are exactly right, and you'll want to use a combination of 
client-side (form) validation and good database design, and make 
decisions based on the individual application.   But, you also need to 
be aware of other people's database design choices, and code around 
them.  It's good to know that a blank string is not null when you need 
to populate a field that is supposed to be NOT NULL but actually has no 
value to be inserted...and that sounds insane, but I've seen stuff like 
this over the years, and sometimes it cannot be changed but only dealt 
with. :)

Also sometimes you're not dealing with forms, let's say you have a web 
service that's sending you XML which gets processed and goes right into 
the database without your ever seeing it in the production environment 
(and this is getting more and more common these days, and there is just 
nobody sitting around reading a generated log of every response that 
comes in to check them, like I suggested you do in a test 
environment...that defeats the purpose of automatically inserting the 
data!), so the best way you're able to generate an error if fields are 
missing is if the query doesn't execute...this can happen, say, if the 
people sending you the data change something and don't bother to notify 
you...if your fields in the database will accept NULL inputs, you could 
look a week later and see missing data...there are a lot of different 
cases and all the solutions are different.

Kristina

> Kristina D. H. Anderson wrote:
> > Let's take a basic example, you design a database table and you 
> > describe your LastName field as NOT NULL.
> > 
> > That means that every time you do an insert into the table, the 
field 
> > LastName MUST contain a value of some sort.  The purpose of that is 
> > basically to ensure that any query, unless it contains all of the 
> > required values, will error out if you attempt to do the insert 
without 
> > having a value in that field.
> 
> But in the case of a form, wouldn't you be validating the input before
> trying to insert the record? Sorry if I seem dense--I must be
> misunderstanding something.
> 
> > In most cases, not all cases, on INSERT, an empty string i.e. '' is 
not 
> > considered a NULL value, but an empty string which will result 
in ... 
> > well...an empty string being stored in your database, but the field 
> > will not show as NULL.  When you pull that out of the database, 
some 
> > languages will treat that as a NULL value and some will treat it as 
an 
> > empty string.
> > 
> > Or, in your database, you can specify NULL as the default value for 
the 
> > field, and therefore if you skip it in an insert, it will default 
to 
> > NULL.  The design of the database depends on the needs of the 
> > application.
> 
> This is really good to know. I guess that answers my previous question
> above. :)
> 
> > As a test of how PHP treats NULL vs. empty strings with your 
database, 
> > 
> > Create values of NULL, ' ' (a string containing a space), '' (an 
empty 
> > string) and pull them out into variables and then you can begin to 
> > determine how, in your application, they are being treated.
> 
> Now that you guys have given me a better understanding of things, I'm
> going to do exactly as you suggested--create a table with some NULL
> values and try playing around to see what happens.
> 
> > In most cases, you should see NULLs and empty strings look very 
> > similar...but not always.  And the devil is definitely in the 
details, 
> 
> Yes, the devil is definitely in the details. I can't tell you how many
> times I've lost a night's sleep over something small. For me, it 
usually
> has to do with logic/sequence, but sometimes it's something really
> stupid like:
> 
> Creating a field/variable named desc and spending hours trying to 
figure
> out why MySQL was balking at my query with its typically enigmatic 
error
> messages. When it finally dawned on me what my mistake was I think I
> invented some entirely new curse words just for that occasion! LOL
> 
> Or like the time (very recently) when I was converting timestamps to
> calculate the number of days between two given dates. I used the
> number_format() function so the number of days display nicely to the
> user, but then couldn't figure out why records with more than 999
> weren't behaving properly (they were supposed to show up in different
> colors as expiration dates approached, and notices of pending
> expirations were also to be emailed). I checked, doubled checked, and
> triple checked my math because numbers are not my friends, but I
> couldn't find anything wrong. The problem? The comma that
> number_format() was inserting. Duh. Once I switched to using the round
()
> and restricted using number_format() only to display things at the end
> after all calculations had been done, everything was just fine.
> 
> > especially if your application is talking to other platforms that 
may 
> > handle these values differently.
> 
> Again, very good to know.
> 
> > It can help you to understand NULL, if you contemplate the 
difference 
> > between an empty string '' and NULL.  NULL is not a string because 
it's 
> > undetermined. But an empty string is a string that contains, well, 
> > nothing, but it is not NULL.
> 
> Got it. Thanks for your help, Kristina.
> 
> Bev
> 
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> 
> http://www.nyphp.org/show_participation.php
> 
>