Saving Null Value

I know that saving data to mySQL is something like this:
mysql_query("INSERT INTO mytable(field1, field2, field3, field4) VALUES ('$firstname', '$middlename', '$lastname', '$address')") or die(mysql_error());

But field2 (middlename) and field4 (address) is optional. It may or may not have a value. In case it is blank, I should insert a NULL value.

Every time it is blank my system always insert a space (not null)

How can I check if a blank entry and then pass a null value? I know I can use the word "null" to pass a null value but I dont know how to implement this. Thanks

By the way my fields allow null value.

 

Are you using NULL without quotes?

VALUES (null, '$middlename', '$lastname', '$address');

PHP:

Jay

 

You can use if statement

Say if the value entered is null than $middlename = "null";

 

No

That would set it to the string "null" that has 4 characters.

$middlename = null;

PHP:

That would be correct. NULL is case in-sensitive.

 

my mistake it should be $middlename = null;

Rep Added

Edit

Cant Give Rep asks to share with other ppl before giving you again

 

If I use this
VALUES (null, '$middlename', '$lastname', '$address');

it insert null. But what Im asking is how can I insert value depending on the user input, Meaning if the user did not enter any value, then I should insert null, but if the user inputted something then I should save it.

If I use this:

$middlename = null;
VALUES ('$middlename', '$lastname', '$address');

it inserts space

 

That because you are inserting 'null character'.

Here's an example of what you want:

if (empty($middlename)){
    $middlename = null;
} else {
    $middlename = "'{$middlename}'";
}
// ...
mysql_query("... VALUES ($middlename, '$lastname', '$address');");

PHP:

I don't really see the point, though. If the value is empty, it's going to be '' anyway :|

 

Hi jayshah,

if I use this
if (empty($middlename)){
$middlename = null;
....

VALUES ('$middlename', '$lastname', '$address');

Im sure it inserts space instead of null

 

That means the value of $middlename is already a space. Make sure it's totally empty. You can change the test:

if (trim($middlename) == ''){

PHP:

Jay

 

Is it submitted using a form? I assume it is:

It may be a space or a newline character (if that input is a textarea).

try var_dump()ing the eg: $_POST['middlename'] and see what it contains

 

Here's my actual code:

if (trim($_POST['middle_name']) == "") {
$my_midname = NULL;
} else {
$my_midname = trim($_POST['middle_name']);
}

... VALUES ('$my_lastname', '$my_midname', '$my_firstname'.....)

and it inserts space instead of null

 

Is your field NOT NULL by default? I mean when you ADD the field, like:

ALTER TABLE `the_table` ADD `middle_name` NOT NULL ?

If yeah, thats the problem i think.

 

Hi ads2help,

My original post says "By the way my fields allow null value."