TangoRangers.com's Blog

EDIT: 03/01/2009
NOTE! This may not be good practice. There are better ways to inserting a TRUE NULL value! This actually inserts the word NULL in the database! You may have to look over your final code and determine if my way was the best way. Also, you only have to do this if you have issues trying to insert the data into MySQL.
End Edit.

The only way this post will help you is if in your MySQL database you have NULL set to YES and DEFAULT as NULL. Most databases are not setup this way, it’s probably pretty rare. See end of post.

Recently I ran into an issue with my php script that inserts data into MySQL that has NULL fields. Here is how I fixed it.

To start in my database I have fields that can accept a NULL value, because of this I had to change the way my data was entered into the database. After I created a form to take the data I then ran it through a process to add slashes (in case there were any single quotes, double quotes, or any other non standard character).

For example, I have a field called title in my database, and in my form. Now lets say that the user of the script doesn’t enter in a title but we still want to insert a record. I need to first determine that there is or is not any data.

if ($_POST['title'])
{
     $title = addslashes($_POST['title']);
}
else
{
     $title = "NULL";
}

Notice that I put NULL in quotations. If you do not quote it you will clear out the variable. This will cause issues in the INSERT query to MySQL.

The MySQL query might look something like this.

$result = mysql_query($query = "INSERT INTO table_name (name, title) VALUES ('$name', '$title')");

I know this doesn’t make much sense, but it’s the way I had to do it because of the way my database was setup. Here is the entire php file.

<?php
if (isset($_POST['submit']))
{
  if (isset($_POST['name']))
  {
    $name = addslashes($_POST['name']);
    if (isset($_POST['title']))
    {
      $title = addslashes($_POST['title']);
    }
    else
    {
      $title = "NULL";
    }
  $result = mysql_query($query = "INSERT INTO table_name (name, title) VALUES ('$name', '$title')");
  }
  else
  {
    echo "Enter a Name!<br />";
  }
}
?>
<form name="form1" action = "<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype='multipart/form-data'>
     <input name="name" type="text" value="name" />
     <input name="title" type="text" value="title" />
     <input name="submit" type="submit" value="submit" />
</form>

In the Database, using something like PHPMyAdmin you might see something like this.

Field———-Type—————–Collation———Attributes——Null——Default——Extra
name——-varchar(30)—-utf8_unicode_ci——–None———-No
title———varchar(30)—-utf8_unicode_ci——–None———-Yes——–NULL

If so you will need to quote your NULL before inserting the data into MySQL. (Sorry that doesn’t line up very well)
Maybe this way will help.