Insert NULL not Blank into MySQL with PHP Script

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mark Davenport
    #1

    Insert NULL not Blank into MySQL with PHP Script

    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
    Tags: insert null
    • Udo Giacomozzi
      #2
      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

        Comment

        • Mark Davenport
          #3
          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

            Comment

            Previous template Next
            Working...