CODEWITHSUNDEEP
phpmysqlinsertnull
MattP
MattP

Reputation: 2863

PHP/MySQL Insert null values

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

Upvotes: 34

Views: 183159

Answers (2)

Micah Fagre
Micah Fagre

Reputation: 67

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

Upvotes: 5

Andrew Moore
Andrew Moore

Reputation: 95334

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.

Upvotes: 71

Related Questions