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_date,
application_time, referring_web_site, keywords, first_name, last_name,
email, address, city, state, zip, home_phone, business_phone,
best_time_to_call)
values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCombined', '$fBusinessPhoneCombined', '$fBestTime')";

$result = mysql_query($query) or die('Failed because:
'.mysql_error());
An example of one of the fields that may need to be null is
$fBusinessPhoneCombined. 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 ($fBusinessPhone1 == ""){
$fBusinessPhoneCombined = '';
}
if ($fBusinessPhone1 == ""){
$fBusinessPhoneCombined = NULL;
}

if ($fBusinessPhone1 == ""){
$fBusinessPhoneCombined = 'NULL';
}
HELP! :-)
Thanks in advance,
- Mark

[Udo Giacomozzi]

da**********@hotmail.com (Mark Davenport) wrote in
news:3b**************************@posting.google.c om:

values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCombined', '$fBusinessPhoneCombined', '$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_string($col1) . "'";
} else {
$col1 = "NULL"; // in the SQL query "NULL" will NOT be quoted
}

It is always a good idea to use mysql_escape_string 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_date,
application_time, referring_web_site, keywords, first_name, last_name,
email, address, city, state, zip, home_phone, business_phone,
best_time_to_call)
values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
'$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
'$fHomePhoneCombined', '$fBusinessPhoneCombined', '$fBestTime')";

"INSERT INTO customers SET application_date='$appDate',
application_time='$appTime', referring_web_site='$Campaign',
keywords='$kw', first_name='$fFirstName', last_name='$fLastName',
email='$fEmail', address='$fAddress', state='$fState', zip='$fZip',
home_phone='$fHomePhoneCombined',
business_phone='$fBusinessPhoneCombined', best_time_to_call='$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
$fBusinessPhoneCombined. 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_date='$appDate',
application_time='$appTime', referring_web_site='$Campaign',
keywords='$kw'";

if(!empty(trim($fBusinessPhone1))
$q.=", business_phone='$BusinessPhone1'";

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