How to insert a null when variables are empty?

I have a form that asks for name, address, company name, etc. Then I do an insert of those variables into the mySQL database. The problem is that if one of the fields is left empty, the new record in the database shows it as an empty instead of a null. What is the best way to get around this?

Thanks!

You could try something like…


<?php
/* Defaults */
$fields = array(
	'name'	=> null,
	'email'	=> null,
  'age'   => null,
);

/* Assign anything that isn't empty */
foreach($fields as $key => &$value){
    if(!empty($_POST[$key])){
        $value = $_POST[$key];
    }
}

/* Remove anything unassigned */
$fields = array_filter($fields);

/* Create SQL */
$sql = sprintf(
    "INSERT INTO table (%s)VALUES('%s');",
    implode(' ,',   array_keys($fields)),
    implode("', '", array_values($fields))
);

That’s a nice way of doing it.

If it’s a quick fix you need, you could also use a ternary thingy to insert a null (this example assumes PDO usage):


$stmt = $db->prepare('UPDATE a_table SET field_1 = ?, field_2 = ? WHERE id = ?');
$stmt->execute(
    array(
        $field_1,
        (!empty($field_2) ? $field_2 : NULL ),
        $id
    )
);