Jump to content

Field with Null not returned


Recommended Posts

I have a field which contains a NULL value.

 

When I run my Prepared Statement, the variable (i.e. $someVariable) that is bound to that field doesn't appear?

 

Am I missing something?

 

Even if the field is NULL, I would still expect the variable to appear in NetBeans when I step through my code...

 

 

Debbie

 

Link to comment
Share on other sites

In NetBeans, for $_POST['birthYear'] I just see a blank for the value which I assume means NULL?

 

s_birthYear does not appear under NetBean's Variable List, even though there is a NULL in that database field.

 

And for some strange reason, this code is evaluating to TRUE even though there isn't an $s_birthYear to compare against $_POST['birthYear']...

 

			// ********************
			// Compare Details.		*
			// ********************
			if (($_POST['firstName'] == $s_firstName)
					&& ($_POST['gender'] == $s_gender)
					&& ($_POST['birthYear'] == $s_birthYear)
					&& ($_POST['location'] == $s_location)
					&& ($_POST['occupation'] == $s_occupation)
					&& ($_POST['interests'] == $s_interests)
					&& ($_POST['aboutMe'] == $s_aboutMe)){

 

 

What is going on?!  :confused:

 

 

Debbie

 

 

Link to comment
Share on other sites

When I run my Prepared Statement, the variable (i.e. $someVariable) that is bound to that field doesn't appear?

 

Am I missing something?

How do you know that the variable doesn't appear? If you are using isset(), this function will return FALSE when the variable is set to a NULL value.

Link to comment
Share on other sites

Verify your variables contain what you're assuming they contain.

 

I'm trying...

 

When I first start stepping through my code, $trimmed['birthYear'] appears in NetBeans as a variable with a value of...

 

After my code hits this line...

 

$trimmed['birthYear']=NULL;

 

 

$trimmed['birthYear'] disappears from the NetBean's variable list

 

Does that sound right?

 

 

Debbie

 

 

Link to comment
Share on other sites

When I run my Prepared Statement, the variable (i.e. $someVariable) that is bound to that field doesn't appear?

 

Am I missing something?

How do you know that the variable doesn't appear? If you are using isset(), this function will return FALSE when the variable is set to a NULL value.

 

Because I can variable go in and out of scope in NetBeans, of course...

 

 

Debbie

 

 

Link to comment
Share on other sites

A variable set to NULL will return false if you use it in isset

 

However, if( $null_var === NULL ) will still return true, and no notices will be thrown.

 

In general, avoid the use of NULL unless you need to implement a feature it offers. In this case, it's 'better' to simply use a blank string, and keep the column as NOT NULL.

Link to comment
Share on other sites

A variable set to NULL will return false if you use it in isset

 

However, if( $null_var === NULL ) will still return true, and no notices will be thrown.

 

In general, avoid the use of NULL unless you need to implement a feature it offers. In this case, it's 'better' to simply use a blank string, and keep the column as NOT NULL.

 

After consulting with some database people I know, I have decided to INSERT a NULL if there is no answer to a Form question since that is the *proper* way to store things in a database.

 

Out of frustration, I moved on to my next Form Field and wrote the following, which seems to do what I want...

 

	// Validate Location. (Optional)
	$location = $trimmed['location'];

	if (empty($location)){
		// Location does Not Exist.
		$location = NULL;

	}else{
		// Location Exists.
		if (strlen($location) <= 40){
				// Valid Location.
				// Continue processing...

		}else{
				// Invalid Location.
				$errors['location'] = 'Location cannot exceed 40 characters.';
		}
	}//End of VALIDATE LOCATION

 

 

I'm not sure why I am having such a hell of a time with $birthYear?!  :shrug:

 

Stepping through it in NetBeans for the upteenth time to see what is going wrong...

 

 

Debbie

 

Link to comment
Share on other sites

I've never worked with NetBeans, but by what you've told us, it appears that NetBeans considers any variable set to NULL to be not set at all, so it does not show up in the variable list.

 

Yeah, I've never noticed that minor but important detail.

 

I think that is a poor design.

 

If I had written NetBeans, I would have "NULL" or "<NULL>" show up in the Value column if a variable was NULL...

 

Anyways?!

 

 

Debbie

 

 

Link to comment
Share on other sites

I was getting some really weird behavior earlier, including a "0" that had showed up that my $birthYear=NULL would NOT overwrite?!  :confused:

 

 

So after re-writing the other - easier - Text Boxes, I came up with this new code which seems to be working...

 

	$birthYear = $trimmed['birthYear'];

	if(empty($birthYear)){
		// Birth-Year does Not Exist (i.e. Null, Empty or 0).
		$birthYear = NULL;

	}else{
		// Birth-Year Exists.
		if(ctype_digit($birthYear)
					&& $birthYear >= $oldestYear
					&& $birthYear <= $newestYear){
			// Valid Birth-Year.
			// Continue processing...

		}else{
			// Invalid Birth-Year.
			// Value Not Integer or Outside Accepted Range.
			$errors['birthYear'] = "Birth Year must be between {$oldestYear} and {$newestYear}";
		}
	}//End of VALIDATE BIRTH-YEAR

 

 

How does that look?

 

Does it handle all Form Inputs, plus any things a hacker could do?

 

 

Debbie

 

 

Link to comment
Share on other sites

After consulting with some database people I know, I have decided to INSERT a NULL if there is no answer to a Form question since that is the *proper* way to store things in a database.

 

They are 'wrong.'

 

A column that allows NULL is larger than one that doesn't.

 

An empty string or zero integer are fine representations of blank fields. In the case of boolean with a third, not-specified option, you might as well use a tinyint. Same size, from what I understand.

 

Again, there are very specific reasons to allow a NULL field. When you understand what those are, it's time to use them.

 

This isn't coming directly from me. It's advice passed by my employer's database engineers. Avoid using NULL unless you NEED to. NULL has very specific, very different behaviour. It's generally both more efficient and easier to not use it.

 

Yeah, I've never noticed that minor but important detail.

 

I think that is a poor design.

 

If I had written NetBeans, I would have "NULL" or "<NULL>" show up in the Value column if a variable was NULL...

 

Anyways?!

 

 

Debbie

 

 

 

NULL has very specific, very different behaviour. As a self-proclaimed newbie, you're treading in deep water by commenting on IDE application designs. They have a plug-in API... If you'd like it to behave differently, you're welcome to modify it's behaviour yourself.

Link to comment
Share on other sites

After consulting with some database people I know, I have decided to INSERT a NULL if there is no answer to a Form question since that is the *proper* way to store things in a database.

 

They are wrong.

 

A column that allows NULL is larger than one that doesn't.

 

An empty string or zero integer are fine representations of blank fields. In the case of boolean with a third, not-specified option, you might as well use a tinyint. Same size, from what I understand.

 

But we're not debating field size.

 

 

Again, there are very specific reasons to allow a NULL field. When you understand what those are, it's time to use them.

 

NULL means "value unknown".

 

If I have a field which is optional, and person chooses to not fill out that field, then it has no value, "value unknown" and so you would want a data-type that describes what exists in the physical world.

 

"Empty String" means you formerly "set" a value, but now no value exists.

 

Never completing a field is NOT an Empty String.

 

Can you use an Empty String like HTML's default behavior?  Sure.

 

But it also causes issues in your database, like you can't write a query that says "Show me all records that were never filled out" which would be something like...

 

SELECT *
WHERE hobbies IS NULL;

 

 

 

This isn't coming directly from me. It's advice passed by my employer's database engineers. Avoid using NULL unless you NEED to. NULL has very specific, very different behaviour. It's generally both more efficient and easier to not use it.

 

I'm sure it's a subjective topic, but using NULL's in a case like mine probably makes more sense.

 

 

Debbie

 

 

Link to comment
Share on other sites

Okay, continue arguing with the people you're asking help from. I'm done trying to be the latter for this thread at least.

 

SELECT * WHERE hobbies = ''

 

You'll get a lot farther in life if you realize not everyone has the same view of the cosmos as you do...

 

I'm not arguing.

 

I was relaying information that was passed on to me by a database expert who has been in the field as long as you have been alive.

 

Go argue with him if you'd like.

 

 

Debbie

 

Link to comment
Share on other sites

Debbie,

  It's your choice as a designer whether or not you want to support null.  As long as you understand the ramifications of that decision.  I have some rules of thumb I go by, but it's good to keep in mind that the use of null can produce some odd behavior, and the guy who invented relational databases (Dr. Ted Codd) recommended that null not be used. 

 

Having read through this thread, I'm not sure I understand the use case enough to offer an opinion either way.  If you're saying that you have a "question/answer" structure, and you're storing all the answers in one table, then that is not the proper structure anyways.

 

What ends up being most important, in terms of null is how you will be querying, and if queries will include the column in question.  If you ever need to have rows returned with NULL values in them, they will not be returned without doing an "IS NULL", so for that reason many people opt to have default values.  Again it entirely depends on you and what you want to do, but I assure you there is no hard and fast rule to follow -- only someone's recommendation.

 

In defense of Xyph, one irrefutable proof in the realm of database design, is that as databases get large, every byte counts, and the smaller tables == better performance and scalability.  So watching bytes is a very good thing.

 

Last but not least, the concept of null in php has nothing at all to do with database nulls.  When you're dealing with strings, in php, a "null" string is the same thing as an empty string.  As for netbeans debugger, the choice of the debugger to not show variables that are null might be a by-product of the debugging api itself, as well as the php garbage collector.  PHP garbage collects any variables that it determines are no longer needed, so it's possible that they are simply doing what they have to do.  This is the nature of php, where all debuggers are inherently required to make remote calls to the server process, and only have available to them what the api provides.

 

 

 

Link to comment
Share on other sites

Debbie,

  It's your choice as a designer whether or not you want to support null.  As long as you understand the ramifications of that decision.  I have some rules of thumb I go by, but it's good to keep in mind that the use of null can produce some odd behavior, and the guy who invented relational databases (Dr. Ted Codd) recommended that null not be used. 

 

I try to avoid NULL's too, but sometimes they are needed.

 

 

Having read through this thread, I'm not sure I understand the use case enough to offer an opinion either way.  If you're saying that you have a "question/answer" structure, and you're storing all the answers in one table, then that is not the proper structure anyways.

 

Glad you brought that up, because I have a Q&A section, and for that I decided to create a many-to-many like this...

 

MEMBER -||--------0<- ANSWER ->0--------||- QUESTION

 

It was a real PITA to code for me, a newbie, but the benefit is that each ANSWER is its own record, so you never have to worry about NULL's.

 

So I agree with you so far.

 

 

What ends up being most important, in terms of null is how you will be querying, and if queries will include the column in question.  If you ever need to have rows returned with NULL values in them, they will not be returned without doing an "IS NULL", so for that reason many people opt to have default values.

 

Honestly, for my current issue, I can't say how I will query or report on things.

 

BTW, currently I am working on my About-Me section which is currently part of my MEMBER table and is a series of optional fields that Members can may or may not answer (e.g. Gender, Birth Year, Location, Hobbies, Interests, Marital Status, etc.)

 

I could do what I did with my Q&A section, but that would be insanely complicated to code?!

 

So I have instead set those fields as "NULL-able" and - as debated above - have decided to INSERT a NULL if the Form Field for any of these fields is blank.

 

For maybe 6 columns, I don't see this as being a major issue, and I could also break these off into their own table and create a one-to-one relationship.

 

I am a purist, and as one, I say "It is wrong to INSERT an Empty String for a Form Field that was never answered and that may not even apply."

 

If there was ever a time to use a NULL, it would be for a free-form Text Box or Text Area that is *optional*!!

 

 

Again it entirely depends on you and what you want to do, but I assure you there is no hard and fast rule to follow -- only someone's recommendation.

 

I agree.

 

 

In defense of Xyph, one irrefutable proof in the realm of database design, is that as databases get large, every byte counts, and the smaller tables == better performance and scalability.  So watching bytes is a very good thing.

 

I suppose, but if I split my table and create a one-to-one, then that will address some performance issues, even if it doesn't really save space.

 

Also, as I have often said, when I have an issue with too many customers to manage, then that will be an awesome problem to have...    8)

 

BTW, when possible, my first chocie with NULL's is to assign a value when it is a finite list (e.g. 0=unknown, 1=male, 2=female)

 

Trust me, I am not NULL-happy!!

 

 

Last but not least, the concept of null in php has nothing at all to do with database nulls.  When you're dealing with strings, in php, a "null" string is the same thing as an empty string.

 

Okay.

 

 

As for netbeans debugger, the choice of the debugger to not show variables that are null might be a by-product of the debugging api itself, as well as the php garbage collector.  PHP garbage collects any variables that it determines are no longer needed, so it's possible that they are simply doing what they have to do.

 

Yes, it is another subjective decision by the developers.

 

For me, I think it would be nice if they were shown, but as you mention, it is likely a larger topic than I give it credit.

 

 

This is the nature of php, where all debuggers are inherently required to make remote calls to the server process, and only have available to them what the api provides.

 

Okay.

 

Thanks for the thoughts!!!

 

 

Debbie

 

 

Link to comment
Share on other sites

Based on my understanding of your reply, if you are just trying to do a series of columns that go into a traditional user profile, then you are doing exactly what just about everyone else does, so I see no issue with that.  Even though you could be a purist and get some flexibility out of the approach, it is absolutely more complicated, not to mention a lot tougher on the database to fully normalize out the profile attributes.  I would typically allow those to be null just as you have, purely for pragmatic reasons.

 

 

 

Link to comment
Share on other sites

Based on my understanding of your reply, if you are just trying to do a series of columns that go into a traditional user profile, then you are doing exactly what just about everyone else does, so I see no issue with that.  Even though you could be a purist and get some flexibility out of the approach, it is absolutely more complicated, not to mention a lot tougher on the database to fully normalize out the profile attributes.  I would typically allow those to be null just as you have, purely for pragmatic reasons.

 

Glad we agree on the approach!

 

Thanks,

 

 

Debbie

 

 

Link to comment
Share on other sites

Last but not least, the concept of null in php has nothing at all to do with database nulls.  When you're dealing with strings, in php, a "null" string is the same thing as an empty string.

 

I disagree with this.

 

<?php

$a = '';
$b = NULL;

if( isset($a) )
echo '$a is set<br>';
// outputs $a is set

if( isset($b) )
echo '$b is set<br>';
// no output

?>

 

NULL has very specific behaviour. It's NOT the same as an empty string... IMO a lack of data should be signified by a 0 or empty string

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.