Professional Web Applications Themes

Insert NULL not Blank into MySQL with PHP Script - PHP Development

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', ...

  1. #1

    Default 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_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
    Mark Davenport Guest

  2. #2

    Default Re: Insert NULL not Blank into MySQL with PHP Script

    [email]davenportm81hotmail.com[/email] (Mark Davenport) wrote in
    news:3bf9b71a.0308190827.3b1c5b33posting.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
    Udo Giacomozzi Guest

  3. #3

    Default Re: Insert NULL not Blank into MySQL with PHP Script

    Justin Koivisto <spamkoivi.com> wrote in message news:<RBs0b.47$1K4.2474news7.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
    Mark Davenport Guest

  4. #4

    Default Re: Insert NULL not Blank into MySQL with PHP Script

    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
    omegadev Guest

Similar Threads

  1. Insert Record php page blank
    By AndLo in forum Dreamweaver AppDev
    Replies: 3
    Last Post: February 17th, 09:29 PM
  2. When is a blank not a null or ''
    By Troels Arvin in forum PostgreSQL / PGSQL
    Replies: 10
    Last Post: February 2nd, 12:43 PM
  3. insert blank row between records
    By mark in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: May 26th, 11:59 AM
  4. INSERT Null value problem
    By Targa in forum ASP Database
    Replies: 8
    Last Post: May 15th, 03:50 AM
  5. ASP SQL Insert NULL Date Value
    By Chad S in forum ASP
    Replies: 5
    Last Post: February 19th, 07:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139