Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Convert empty string to null automatically

    Hi,

    Is there a setting for MySQL 4.1 to automatically convert empty strings to null when inserting and updating?

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I do not know of and could not find a setting to do this.

    There are three ways to get a NULL to replace an empty string (assume in the following a table with name and info columns) -

    1) Don't list the columns that are empty strings. The following query will insert a name with a NULL for info.
    PHP Code:
    $query "INSERT INTO table (name) values ('$myname')"
    2) Explicitly put the NULL keyword in the query (note that you cannot put a '$myinfo' variable, with or without quotes, and set it to NULL or 'NULL' and have it work) -
    PHP Code:
    $query "INSERT INTO table (name,info) values ('$myname',NULL)"
    3) Use an IF statement to give a NULL or the original value -
    PHP Code:
    $query "INSERT INTO table (name,info) values (IF(LENGTH('$myname')=0,NULL,'$myname'),IF(LENGTH('$myinfo')=0,NULL,'$myinfo'))"

  3. #3
    Join Date
    Oct 2016
    Posts
    1

    This code is not working

    $query = "INSERT INTO table (name,info) values (IF(LENGTH('$myname')=0,NULL,'$myname'),IF(LENGTH( '$myinfo')=0,NULL,'$myinfo'))";

    when i'll execute this code not store null values in my database

Posting Permissions

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