How Can I Store True NULL Values in MySQLi Prepared Statements?

Storing True NULL Values in MySQLi Prepared Statements

In a prepared statement within MySQLi, the default behavior for a NULL value is to convert it to an empty string ('') in the case of a string or 0 in the case of an integer. However, if you prefer to preserve the NULL value, there is a specific approach you can take.

Using the MySQL NULL Safe Operator

To store a true NULL in a MySQLi prepared statement, you must utilize the MySQL NULL safe operator:

<=>

This operator ensures that a column is compared for equality or non-equality to NULL. For instance:

$price = NULL; // Note: Using php NULL without quotes
$stmt = $mysqli->prepare("SELECT id FROM product WHERE price <=> ?"); // Selects products with NULL prices
$stmt->bind_param($price);

In this example, the query will retrieve products where the price column is NULL. Without the NULL safe operator, the query would have returned no results, as NULL is not equal to an empty string or 0.

Example

The following PHP code demonstrates how to use the NULL safe operator in a prepared statement:

$firstName = NULL;
$lastName = "Doe";

$stmt = $mysqli->prepare("UPDATE users SET first_name <=> ?, last_name = ?");
$stmt->bind_param("ss", $firstName, $lastName);
$stmt->execute();

This query would update the user with the specified last name and preserve the NULL value for the first name.

The above is the detailed content of How Can I Store True NULL Values in MySQLi Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!