How Can I Properly Insert NULL Values into a MySQL Database Using PHP?

Inserting Null Values in PHP/MySQL

When inserting data into a database, it's essential to handle null values correctly. In this case, we want to insert null into a database field if the corresponding value in the array is also null.

Problem:

Consider the following code:

$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']});
}

When one of the null_field values is null, the insert statement fails. This is because PHP interprets null as an empty string, which results in a syntax error in the SQL query.

Solution:

The recommended approach to handling null values is to use prepared statements. Prepared statements automatically differentiate between null and an empty string and insert the correct value.

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

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

$stmt->execute();

Prepared statements also prevent SQL injection attacks by escaping values and ensuring that the query is properly formatted. Therefore, it's recommended to use mysqli and its prepared statements instead of the deprecated mysql extension.

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