PHP

MySQL

Handle NULLs in INSERT/UPDATE

Thieved from http://php.net/manual/en/function.mysql-query.php

This is a small function I wrote for handling NULL values in MySQL. (See examples below)

<?php
function parseNull($data)
{
        // Be sure your data is escaped before you use this function
    if (chop($data) != "")
        if (strtolower(chop($data)) == "null")
            return "NULL";
        else
            return "'" . $data . "'";
    else
        return "NULL";
}
?>

When you pass it a value, it checks if it is a blank string or the string 'null'. If so, it returns NULL. If not, it wraps your data in single quotes. This allows you to more easily default a field to null when you insert or update data into your database. To use it, set up a mysql insert or update query for a text field without the single quotes, like this:

<?php
// Escape your data before using it with parseNull()
$my_data = mysql_escape_string($my_data);

mysql_query("INSERT INTO `my_table` VALUES (1," . parseNull($my_data). ")");
?>

When $my_data contains the string "null" or a blank string, the value NULL is inserted. When it is not, the value is wrapped in single quotes and this string is inserted.

Warning: Escape the data before passing it to parseNull! parseNull does not escape the data. If you escape after using the parseNull function, the single quotes that parseNull generates will be escaped.

Examples: When passed the ALREADY ESCAPED string "mydata", parseNull returns: 'mydata' The resulting mysql query, using the code above, is:

INSERT INTO `my_table` VALUES (1, 'mydata')

When passed the ALREADY ESCAPED string "null", parseNull returns: NULL The resulting query is: INSERT INTO `my_table` VALUES (1, NULL)

When passed the ALREADY ESCAPED blank string "", parseNull returns: NULL The resulting query is: INSERT INTO `my_table` VALUES (1, NULL)