使用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.