How to Properly Insert NULL Values into MySQL from PHP?

PHP/MySQL Insert Null Values: A Comprehensive Resolution

MySQL databases allow null values in various fields. However, inserting null values using traditional PHP/MySQL queries can be problematic when some array values are null.

Issue:

In the provided query:

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

If $row['null_field'] is null, it will result in an empty column in table2 instead of a null value.

Solution: Prepared Statements

Using prepared statements with the newer mysqli extension offers a robust solution:

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

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

$stmt->execute();

Advantages of using prepared statements:

• They automatically convert null values to SQL NULL.
• They prevent SQL injection attacks by escaping values before execution.
• They simplify parameter binding, removing the need for manual concatenation of query parameters.

By utilizing prepared statements, you can effortlessly insert null values into MySQL databases and avoid potential issues arising from traditional queries.

The above is the detailed content of How to Properly Insert NULL Values into MySQL from PHP?. For more information, please follow other related articles on the PHP Chinese website!