using nulls in a mysqli prepared statement

Question

In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL. Is there any way of doing this?

Answer 1

It's possible to bind a true NULL value to the prepared statements (read this).

You can, in fact, use mysqli_bind_parameter to pass a NULL value to the database. simply create a variable and store the NULL value (see the manpage for it) to the variable and bind that. Works great for me anyway.

Thus it'll have to be something like:

<?php
    $mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

    // person is some object you have defined earlier
    $name = $person->name();
    $age = $person->age();
    $nickname = ($person->nickname() != '') ? $person->nickname() : NULL;

    // prepare the statement
    $stmt = $mysqli->prepare("INSERT INTO Name, Age, Nickname VALUES (?, ?, ?)");

    $stmt->bind_param('sis', $name, $age, $nickname);
?>

This should insert a NULL value into the database.

Answer 2

For anyone coming looking at this because they are having problems binding NULL in their WHERE statement, the solution is this:

There is a mysql NULL safe operator that must be used:

<=>

Example:

<?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($price);
?>