使用mysqli_stmt_bind_param()绑定NULL值

问题描述:

I'm trying to convert a code I write to use the php function mysqli_stmt_bind_param() instead of replacing tag in prewritten statement which I believe is not a good pratice.
So here one of the function I have today:

$idTag1="###";
$requestReplaceArray = array("%PRODUCT_ID%","%PLATFORM_ID%","%STATUS_ID%","%DATE%","%COMMENT%",$idTag1);
$updateRequest="UPDATE REQUEST
                SET
                    id_product = %PRODUCT_ID%,
                    id_platform = %PLATFORM_ID%,
                    id_status = %STATUS_ID%,
                    last_modifier = '".$_SERVER['PHP_AUTH_USER']."',
                    last_modification_date='%DATE%',
                    last_comment = '%COMMENT%'
                WHERE id =".$idTag1;


function updateRequest($id, $productID, $platformID, $statusID, $date, $comment){
    global $requestReplaceArray, $updateRequest;
    escapeArguments($id, $productID, $platformID, $statusID, $date,$comment);
    /*if number value is empty replace by 'null'*/
    $productID=replaceEmptyValueByNull($productID);
    $platformID=replaceEmptyValueByNull($platformID);
    $statusID=replaceEmptyValueByNull($statusID);

    $dbConnection = getDbConnection();
    $replacement = array($productID, $platformID, $statusID,$date ,$comment, $id);
    $updateRequest = str_replace($requestReplaceArray, $replacement, $updateRequest);


    if(! $result = mysqli_query( $dbConnection, $updateRequest ) ) {
       mysqli_free_result($result);
        $dbConnection->close();
        return "Error MyU02, can't update the request #".$id;
    }else{
        mysqli_free_result($result);
        $dbConnection->close();
        return $id; 
    }
}

This code isn't perfect but it works except when a $date is NULL.

My objectives is to now use that synthax :

$requestReplaceString = "iiissi";
$updateRequest="UPDATE REQUEST
            SET
                id_product = ?,
                id_platform = ?,
                id_status = ?,
                last_modifier = '".$_SERVER['PHP_AUTH_USER']."',
                last_modification_date=?,
                last_comment = ?
            WHERE id =?";


function updateRequest($id, $productID, $platformID, $statusID, $date, $comment){
    global $requestReplaceString, $updateRequest;
    $dbConnection = getDbConnection();

    $stmt = mysqli_prepare( $dbConnection, $updateRequest);
    mysqli_stmt_bind_param($stmt, $requestReplaceString, $productID, $platformID, $statusID,$date ,$comment, $id);


    if(mysqli_stmt_execute($stmt) ) {
        mysqli_stmt_close($stmt);
        $dbConnection->close();
        return $id; 
    }else{
        mysqli_stmt_close($stmt);
        $dbConnection->close();
        return "Error MyU02, can't update the request #".$id;
    }
}

My main issue here is to set some value to null because trying to bind a php NULL is not working at all. So my question is how can I bind NULL with mysqli if it's possible ?

Edit: it does work and my mistake comes from my bad code, the code is now corrected.

Just bind it. It works perfectly. Any null value will be sent to server as mysql's NULL.

Also note that there is a fatal issue with your code: you are connecting to database for the every query. Which will just kill your server.