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

I'm p开发者_如何学JAVAassing 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().

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

Use $int_value for the column in your INSERT.

The question is not how to send NULL to MySQL via mysqli, but rather how to get NULL value from the GET parameters.

I would suggest two ways. Either use filter_input() which is a really handy function or provide null as a default value if no value was passed in the URL query.

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT, FILTER_NULL_ON_FAILURE);
// or
$id = empty($_GET['id']) ? null : (int) $_GET['id'];

// If no value was presented in GET then $id will be passed as NULL
$stmt->bind_param('s', $id);

In my opinion filter_input() provides more correct outcome. See this comparison:

| $_GET['id']   | filter_input | Cast to int |
|---------------|--------------|-------------|
| Inexistent    | false        | NULL        |
|               | NULL         | NULL        |
| <white space> | NULL         | 0           |
| 0             | 0            | NULL        |
| 42            | 42           | 42          |
| 42.333        | NULL         | 42          |

You might want to look at your database schema to see if the column in question even allows for null values. If the column does not allow nulls, than it will use the default value for the field when you try to put in a null value… which is usually zero for integer fields.

I had this problem and just changed the relevant DB columns from INT to VARCHAR. Empty values are now stored as NULL and a 0 is a 0. Even deleting the 0 in PHP MyAdmin would see a '0' pop back up, so I guess INT equates empty with zero and VARCHAR doesn't.

For MySQLi, you can't pass a constant like NULL or 0 or even "NULL" as a bind_param() argument.

Everything passed must be a reference type. It's a stupid limitation, I know.

But working around it is easy. Just create a throw-away variable. In a project I have been working on, I needed insert some data to a BLOB field. Unfortunately, MySQLi doesn't let you bind binary data if it's longer than a certain length. You have to bind NULL and then follow-up with sending the binary info via send_long_data().

But, again, you cannot bind a constant like NULL, so in the example, look where I used the variable $Dummy.

$InfoToInsert = array
(
    'a number' => 9000,
    'a double' => 9000.0,
    'a string' => 'Hello World',
    'a blob' => gzdeflate('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed')
);
$Stmnt =
(
    'INSERT INTO _mytable '.
        '(_myintcol, _mydoublecol _mystringcol, _myblobcol) '.
    'VALUES '.
        '(?, ?, ?, ?)'
);
if(!$PreparedStmnt = $Conn->prepare($Stmnt))
{
    $OutputError = 'Failure preparing statement: '.my_SanitizeHtml($Conn->error).'<br /><br />Full query:<br />'.$Stmnt;
    $Conn->rollback();
    $Conn->close();
    return(false);
}
$Dummy = null;
$PreparedStmnt->bind_param
(
    'idsb',                         // Possible types:
                                    //     i for Integer
                                    //     d for Double
                                    //     s for String
                                    //     b for BLOB

    $InfoToInsert['a number'],      // _myintcol
    $InfoToInsert['a double'],      // _mydoublecol
    $InfoToInsert['a string'],      // _mystringcol
    $Dummy                          // _myblobcol
);
$PreparedStmnt->send_long_data(3, $InfoToInsert['a blob']); // 3 because it's the 4th parameter (-1 for zero-based)
if(!$PreparedStmnt->execute())
{
    $OutputError = 'Failure inserting report: '.my_SanitizeHtml($Conn->error);
    $PreparedStmnt->close();
    $Conn->rollback();
    $Conn->close();
    return(false);
}
$PreparedStmnt->close();