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

Jul 16 '05 #1

Udo Giacomozzi

davenportm81@hotmail.com (Mark Davenport) wrote in
news:3bf9b71a.0308190827.3b1c5b33@posting.google.c om:
[color=blue]
> values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
> '$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
> '$fHomePhoneCombined', '$fBusinessPhoneCombined', '$fBestTime')";[/color]

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

Jul 16 '05 #2

Mark Davenport

Justin Koivisto <spam@koivi.com> wrote in message news:<RBs0b.47$1K4.2474@news7.onvoy.net>...[color=blue]
> Mark Davenport wrote:[color=green]
> > 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.[/color]
>
> first, be sure the field doesn't specify NOT NULL.[/color]

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

[color=blue]
>[color=green]
> > $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')";[/color]
>
> "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.
>[color=green]
> > 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:[/color]
>
> 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[/color]


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

Take care,
- Mark

Jul 16 '05 #3