Insert NULL not Blank into MySQL with PHP Script

Re: Insert NULL not Blank into MySQL with PHP Script

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


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

Take care,
- Mark

Re: Insert NULL not Blank into MySQL with PHP Script

davenportm81@ho tmail.com (Mark Davenport) wrote in
news:3bf9b71a.0 308190827.3b1c5 b33@posting.goo gle.com:
[color=blue]
> values ('', '$appDate', '$appTime', '$Campaign', '$kw', '$fFirstName',
> '$fLastName', '$fEmail', '$fAddress', '', '$fState', '$fZip',
> '$fHomePhoneCom bined', '$fBusinessPhon eCombined', '$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_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

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