PHP/MySQL Insert null values

Question

I'm struggling with some PHP/MySQL code. 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

score 67 · Accepted Answer · answered Mar 16 '11 at 18:13

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.

@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). — outis, Jan 17 '12 at 20:44

score 4 · Answer 2 · answered Mar 13 '14 at 21:31

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

score 4 · Answer 3 · answered Mar 16 '11 at 17:58

4

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..

answered Mar 16 '11 at 17:58

mooreds

4,932
2
32
40

Thanks, i'll have to add some additional testing and explicitly set the nulls, it is much more logical in the application that the fields are null rather than empty strings. – MattP Mar 16 '11 at 18:04
1

Unfortunately, quoting the field will be incorrect, if the value to be inserted is NULL. Can't do the entire statement in one string; will need something like `mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', " . (is_null($row['null_field']), "NULL", "'{$row['null_field']}'") . ")"` Which can be made easier to read by creating a helper function `function quotedValueOrNull( $value ) { return (is_null($value), "NULL", "'{$value}'"); }` – ToolmakerSteve Nov 04 '15 at 17:40
This comment is irrelevant to the question asked and promotes a horrible practice. I don't see any reason why it should be here. – Your Common Sense Mar 09 '23 at 17:44

PHP/MySQL Insert null values

3 Answers3

Linked

Related