How do I get NULL into a MySQL integer column with PHP/MySQLi?

I’m passing values into a PHP page through the URL and using them in queries with MySQLi. The problem is that an empty string gets converted to zero for an integer column, when in fact it needs to be NULL.

How do I get NULL into an integer column with PHP/MySQLi, from a param passed in the URL?

Update: Here’s an example of how I’d use the incoming parameter and a prepared statement to do the insert. The one I need to be NULL on the DB is $specialtyType.

function InsertItem($itemID, $ownerID, $specialtyType)
{
    $this->insertItemStmt->bind_param("ssi", $itemID, $ownerID, $specialtyType);
    $this->insertItemStmt->execute();

If I add Jason’s suggested line before the bind_param (setting up an intermediate variable and using it in the bind_param instead of $specialty_type), it winds up containing 0 after the execute().

Answer

$int_value = empty($_GET['int_value']) ? NULL : (int)$_GET['int_value'];

Use $int_value for the column in your INSERT.

Advertisement

Answer