How do you properly set a blank variable as NULL

Question

I am parsing an XML feed into a MYSQL table using simplexml_load_file(). Some of the variables are blank, I would like them to be actual NULL instead of NULL, or is there even a difference?

$xml = simplexml_load_file('cb.xml') or die ('Bad XML File');
foreach($xml->item as $item) {
    $name = $item->name;

//Tried

if ($name == '') {
    $name = 'NULL';
}

//And

if ($name == '') {
    $name = NULL;
}

mysql_query("INSERT INTO cb (name) VALUES ('$name')");

Answer 1

The second variable initialization is correct; the first is just the string 'NULL' (which is not special from PHP's viewpoint). However, you should be using prepared statements (MySQLi_STMT or PDOStatement. If you want to stick with the regular mysql extension, use mysql_real_escape_string

An example with PDO is:

$stmt = $pdo_con->prepare("INSERT INTO cb (name) VALUES (?);");
$stmt->execute(array($name));

This will handle nulls correctly, unlike your current string interpolation.

Answer 2

This is because you're giving MySQL a string:

.... ('ANYTHING WITHIN QUOTES IS A STRING')

And null, when "casted" to a string, becomes an empty string. So your first try gave ... ('NULL'), and now it gives ... ('').

You must use the NULL keyword inside the query, without quotes, to insert NULL into a database field.

mysql_query("INSERT INTO cb (name) VALUES (" . ($name == null ? "NULL" : "'$name'") . ")");

Oh, and as usual, take care not to get SQL-injected with your unprotected $name variable.