PHP/MySQL Insert null values
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.
I think you need quotes around your {$row['null_field']}
, so '{$row['null_field']}'
If you don't have the quotes, you'll occasionally end up with an insert statement that looks like this: insert into table2 (f1, f2) values ('val1',)
which is a syntax error.
If that is a numeric field, you will have to do some testing above it, and if there is no value in null_field, explicitly set it to null..
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).")");