How to Preserve True NULL Values in MySQLi Prepared Statements?

Manipulating NULL Values in MySQLi Prepared Statements

When using MySQLi prepared statements, handling NULL values can be a bit tricky. By default, MySQLi converts NULL values into empty strings ('') for strings and 0 for integers. However, there are scenarios where you might want to preserve the actual NULL value.

Preserving True NULL Values

To store a true NULL value in a MySQLi prepared statement, you can utilize the MySQL NULL Safe Operator (<>=). This operator allows you to compare NULL values correctly.

Example:

<code class="php">$price = NULL;
$stmt = $mysqli->prepare("SELECT id FROM product WHERE price <> ?");
$stmt->bind_param('s', $price); // Bind the price as a string</code>

In this example, the stmt will select rows from the 'product' table where the 'price' column is NULL. The <> operator is used to check for both NULL and non-NULL values, allowing you to accurately handle NULL comparisons.

Additional Notes:

• When using the NULL safe operator, you must use the correct binding format based on the column's data type. For example, bind a NULL integer as an integer type instead of a string type.
• Be cautious when using the NULL safe operator with values other than NULL. It can produce unexpected results if used with non-NULL values.

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