In the past, I hadn’t given much thought to NULL vs. empty string when dealing with blank fields in my database. If something was empty, I would just set it to $field = ''; and insert it. This is bad for a few reasons—but most importantly the database doesn’t see an empty string as an empty field, but rather a string with no length. When using COUNT, it sees empty strings and other data as one and the same.

Once I started using NULL values in my DBs, I found the PHP more cumbersome when doing inserts.

Take a generic insert statement:
INSERT INTO table (field1, field2) VALUES ('$value1', '$value2')

If $value1 is NULL, then the word 'NULL' will be inserted as a string and not a NULL value, because the variable is enclosed in quotes. The insert statement would have to be changed to VALUES ($value1, '$value2'). If a variable was NULL, no quotes around it; if the variable was not NULL, it would need quotes. What a pain to have to alter the syntax of the insert statement depending on which variables are NULL. There had to be another way.

I quickly found an easy solution that involved dealing with NULL values at the same time you were sanitizing values for the DB. Using that idea, I made this DB prep function:

function db_prep($data)
// Basic prep function - trims and escapes data
{
   if (isset($data) and $data != ''){
      $prepped = "'" . mysql_real_escape_string(trim($data)) . "'";
   }
   else {
      $prepped = "NULL";
   }
   return $prepped;
}

One important change that I made to the original code was the conditional statement that determined whether it was a NULL value or not. The original code uses if ($data). This will correctly return false if the variable is an empty string or a NULL value, but if its value is 0, it will also return false. Because I would be using this function to batch process many values from multiple sources, I don’t want it to set every numeric 0 to NULL. Changing it to if (isset($data) and $data != '') takes care of that problem. For a list of how each comparison operator will handle different types of data, look at this table from PHP.net.

In use:

$data1 = NULL;
$data2 = "string";
 
$value1 = db_prep($data1);
$value2 = db_prep($data2);
 
$query = "INSERT INTO table (field1, field2) VALUES ($value1, $value2)";

You no longer have to worry about which values are NULL, and which values require quotes in the insert statement.

This is a very simple function that I started using a long time ago, and became the foundation of all different data-prepping functions I’d use on future sites. As with everything I’ll be writing about, this journal is my humble contribution to the search engine gods of the internet that have given me so much over the years. If just one person shows up here after searching for a solution to their problem, and I end up helping them, I’ll be happy.

I welcome any questions, ideas or criticisms—so feel free to comment.

Tags: MySQL, PHP