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

The comments to the PHP documentation on mysqli_stmt::bind_param indicate that passing in NULL was not easily possible.

Solutions offered in the comments do some pre-preparation work on the prepared statement, replacing the "?" markers with "NULL" for every param that has the PHP null value. The modified query string is then used.

The following function is from user comment 80119:

function preparse_prepared($sQuery, &$saParams)
{
    $nPos = 0;

    $sRetval = $sQuery;
    foreach ($saParams as $x_Key => $Param)
    {
        //if we find no more ?'s we're done then
        if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
        {
            break;
        }

        //this test must be done second, because we need to 
        //increment offsets of $nPos for each ?.
        //we have no need to parse anything that isn't NULL.
        if (!is_null($Param))
        {
            continue;
        }


        //null value, replace this ? with NULL.
        $sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);

        //unset this element now
        unset($saParams[$x_Key]);
    }
    return $sRetval;
}

(It's not really the coding style I would have done it in, but if it works...)

Answer 2

by my side, i store every parameters in an array and pass them in Bind_param function by array_shift($myArray). NULL is accepted like that.. S.