PHP/MySQL Insert null values

Question

I am reading from 1 table, changing some fields then writing to another table, nothing happens if inserting and one of the array values is null when I would like it to insert null in the database (null values are allowed for the field). It looks a bit like this:

$results = mysql_query("select * from mytable");
while ($row = mysql_fetch_assoc($results) {
    mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', {$row['null_field']});
}

Not every row has a null value and in my query there are more fields and 2 columns which may or may not be null

Andrew Moore · Accepted Answer · 2011-03-16 18:13:01Z

71

This is one example where using prepared statements really saves you some trouble.

In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', NULL);

However, if you want to insert a value in that field, you must now branch your code to add the single quotes:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', 'String Value');

Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.

answered Mar 16, 2011 at 18:13

Andrew Moore

95.8k30 gold badges167 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

outis Over a year ago

@MattP: Note that PDO has the same behavior. Compare the API for each before you settle on one (PDO is easier to use and largely DB agnostic, mysqli provides more direct access to the MySQL API).

Micah Fagre · Accepted Answer · 2014-03-13 21:31:08Z

5

For fields where NULL is acceptable, you could use var_export($var, true) to output the string, integer, or NULL literal. Note that you would not surround the output with quotes because they will be automatically added or omitted.

For example:

mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', ".var_export($row['null_field'], true).")");

answered Mar 13, 2014 at 21:31

Micah Fagre

671 silver badge2 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL Insert null values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related