How to Insert Null Values into a MySQL Database with PHP?

PHP/MySQL Insert Null Values

Inserting null values into a MySQL database can be tricky, especially if you're using outdated PHP functions like mysql_query().

When attempting to insert an array value of null using mysql_query(), you may encounter issues where null values aren't properly inserted, despite allowing them in the database.

Solution: Prepared Statements

To address this problem, consider using prepared statements instead. Prepared statements automatically handle null values correctly, eliminating the need for manual branching or additional logic.

In MySQL:

INSERT INTO table2 (f1, f2) VALUES ('String Value', NULL);

However, if you need to insert a non-null value, you'll still need to branch your code to add single quotes:

INSERT INTO table2 (f1, f2) VALUES ('String Value', 'String Value');

Using mysqli and Prepared Statements

Here's an example using MySQLi and prepared statements to handle null values:

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

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

$stmt->execute();</code>

Prepared statements automatically escape fields, handle different types correctly, and prevent parameter-binding errors. They simplify the process and make handling null values in database insertions much easier.

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