Insert NULL not Blank into MySQL with PHP Script

[Mark Davenport]

Hi,

Here's my question: How do I pass a NULL value in a variable to a
MySQL DB?

Here's an overview of my problem: I have a PHP page that processes
code, then inserts the code into a database. Very straightforward .
But, some NULL values are being inserted as a blank space, or the
string "NULL" instead of a true NULL.

Below is the db insert...

$db = mysql_connect ("localhost" , "user name", "password") ;
mysql_select_db ("database name");

$query = "insert into customers (customer_id, application_dat e,
application_tim e, referring_web_s ite, keywords, first_name, last_name,
email, address, city, state, zip, home_phone, business_phone,
best_time_to_ca ll)
values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCom bined', '$fBusinessPhon eCombined', '$fBestTime')";

$result = mysql_query($qu ery) or die('Failed because:
'.mysql_error() );
An example of one of the fields that may need to be null is
$fBusinessPhone Combined. If the user does not enter a telephone number
into the business phone field, then I would like to insert a NULL
value into the database. I tried the following code (at different
times) to make the variable pass a null value, but these don't work. I
either get a blank entry, or the string "NULL" inserted into the DB:

if ($fBusinessPhon e1 == ""){
$fBusinessPhone Combined = '';
}
if ($fBusinessPhon e1 == ""){
$fBusinessPhone Combined = NULL;
}

if ($fBusinessPhon e1 == ""){
$fBusinessPhone Combined = 'NULL';
}
HELP! :-)
Thanks in advance,
- Mark

[Udo Giacomozzi]

da**********@ho tmail.com (Mark Davenport) wrote in
news:3b******** *************** ***@posting.goo gle.com:

values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCom bined', '$fBusinessPhon eCombined', '$fBestTime')";

Don't use quotes if you want to insert NULL values. Instead write:

values ($col1, $col2, $col3 .......

Then add quotes to variables that have a value and set all other to be
"NULL":

if ($col1) {
$col1 = "'" . mysql_escape_st ring($col1) . "'";
} else {
$col1 = "NULL"; // in the SQL query "NULL" will NOT be quoted
}

It is always a good idea to use mysql_escape_st ring but not really
necessary to solve this problem.
Hope this helps,
Udo

--
To reply by e-mail, use following address:
udonews AT nova-sys.net

[Mark Davenport]

Justin Koivisto <sp**@koivi.com > wrote in message news:<RB******* ********@news7. onvoy.net>...

Mark Davenport wrote:

Here's an overview of my problem: I have a PHP page that processes
code, then inserts the code into a database. Very straightforward .
But, some NULL values are being inserted as a blank space, or the
string "NULL" instead of a true NULL.
first, be sure the field doesn't specify NOT NULL.

Yep, I checked that. Thanks for mentioning it though! :-)

$db = mysql_connect ("localhost" , "user name", "password") ;
mysql_select_db ("database name");

$query = "insert into customers (customer_id, application_dat e,
application_tim e, referring_web_s ite, keywords, first_name, last_name,
email, address, city, state, zip, home_phone, business_phone,
best_time_to_ca ll)
values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCom bined', '$fBusinessPhon eCombined', '$fBestTime')";

"INSERT INTO customers SET application_dat e='$appDate',
application_tim e='$appTime', referring_web_s ite='$Campaign' ,
keywords='$kw', first_name='$fF irstName', last_name='$fLa stName',
email='$fEmail' , address='$fAddr ess', state='$fState' , zip='$fZip',
home_phone='$fH omePhoneCombine d',
business_phone= '$fBusinessPhon eCombined', best_time_to_ca ll='$fBestTime' ;

Notice that customer_id and city where not included in the statment. By
doing this, MySQL will assume that the value is NULL.

An example of one of the fields that may need to be null is
$fBusinessPhone Combined. If the user does not enter a telephone number
into the business phone field, then I would like to insert a NULL
value into the database. I tried the following code (at different
times) to make the variable pass a null value, but these don't work. I
either get a blank entry, or the string "NULL" inserted into the DB:

Construct your query as you decide what should be included....

$q="INSERT INTO customers SET application_dat e='$appDate',
application_tim e='$appTime', referring_web_s ite='$Campaign' ,
keywords='$kw'" ;

if(!empty(trim( $fBusinessPhone 1))
$q.=", business_phone= '$BusinessPhone 1'";

Repeat the if statement for each of the fields that would possibly be
NULL. Then at the end, execute the query.

HTH

This worked perfectly! Thanks Justin. The next beer's on me! :-)

Take care,
- Mark