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]

[email]davenportm81@hotmail.com[/email] (Mark Davenport) wrote in
news:3bf9b71a.0308190827.3b1c5b33@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 <spam@koivi.com> wrote in message news:<RBs0b.47$1K4.2474@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

[omegadev]

There is a simple solution to this, and it involves using a case statement inside the sql query. For instance, if doing an update you would set up the query like this:

UPDATE
TABLE_NAME
SET
VALUE_1 = '".$SomeValue."',
VALUE_2 = CASE WHEN '".$SomeValue2."' == -1 THEN NULL WHEN '".$SomeValue2."' <> -1 THEN '".$SomeValue."' END'
WHERE
YOUR_CONDITION