CODEWITHSUNDEEP
phpmysql
ZaneDeFazio
ZaneDeFazio

Reputation: 509

How do you properly set a blank variable as NULL

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')");

Upvotes: 2

Views: 8075

Answers (3)

Hunter
Hunter

Reputation: 1

if you're using more than one value and shorthand if else does work then do this: (we will insert the null data in column 3) * Notice that the single quotes are omitted.. this is because "NULL" cannot be entered in as a string for sql or else it will be a string NULL not an SQL NULL which is what we want.

//condition
if ($col3_var !== NULL)
   $col3_var = "'$col3_var'";
else
   $col3_var = "NULL"; //SQL will omit the "" here

$sql = "INSERT INTO tablename
       (col1, col2, col3...)
       VALUES
       ('$col1_var', '$col2_var', $col3_var)";

$result = mysql_query($sql) or die("Err: " . mysql_error());

Upvotes: 0

zneak
zneak

Reputation: 138261

This is because you're giving MySQL a string:

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

And the PHP null value, 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.

Upvotes: 5

Matthew Flaschen
Matthew Flaschen

Reputation: 285057

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.

Upvotes: 2

Related Questions