CODEWITHSUNDEEP
phpmysqli
Kiwy
Kiwy

Reputation: 346

Bind NULL value with mysqli_stmt_bind_param()

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.

Upvotes: 0

Views: 391

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157919

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.

Upvotes: 1

Related Questions