How to Store Genuine NULL Values in MySQLi Prepared Statements?

Nulls in MySQLi Prepared Statements

In MySQLi prepared statements, NULL values are automatically converted to empty strings for strings and zeros for integers. This may not always be desirable, especially when you want to store NULL as a genuine null value.

Solution: MySQL NULL Safe Operator

To store NULL values correctly in a MySQLi prepared statement, you must use the MySQL NULL safe operator, "<=>":

Syntax:

field_name <=> ?

Example:

Consider the following PHP code:

<code class="php">$price = NULL; // Note: no quotes - using PHP NULL
$stmt = $mysqli->prepare("SELECT id FROM product WHERE price <=> ?"); // Will select products where the price is NULL
$stmt->bind_param('i', $price);</code>

This code will correctly store NULL in the database when the $price variable is NULL.

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