2

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')");
ZaneDeFazio
  • 499
  • 2
  • 6
  • 16

3 Answers3

5

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.

zneak
  • 134,922
  • 42
  • 253
  • 328
2

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.

Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • Can you give an example of a prepared statement? Because when I use just NULL instead of 'NULL' MYSQL still shows a blank field. – ZaneDeFazio Jul 30 '10 at 05:19
  • Here's a [previous answer I gave explaining prepared statements](http://stackoverflow.com/questions/3340533/filtering-out-mysql-query-in-php/3340616#3340616). Know them, use them, love them. – Charles Jul 30 '10 at 05:26
0

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());
Hunter
  • 1