Inserting/Updating null values into db with PHP

Say you have a table with the below structure.

CREATE TABLE foo (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  column0 VARCHAR(45)
  PRIMARY KEY(`id`)
)

Now, say you want to insert null values into column0. Below is the php code to do it.

$column0_value = is_null($column0_value) ? "NULL" : "'" . $this->db->escapeSimple($column0_value) . "'";
<db_handle>->query(
  "INSERT INTO foo (column0) VALUE  ($column0_value)
);

A faulty way which will not insert null values. Below will insert a blank string instead of NULL.

$column0_value = is_null($column0_value) ? NULL : $this->db->escapeSimple($column0_value);
<db_handle>->query(
  "INSERT INTO foo (column0) VALUE  ("$column0_value")
);

PHP, Programming

July 28, 2010

1 Comment