Hi all,
How would one make this "hard coded" prepared statement for select, into a dynamic one that may or may not have some of the variables present?
//$connection made to db
$hardCodedSTMT = "SELECT Col1, Col2, Col3, Col4, Col5 FROM TABLE1 WHERE Col1 = ? AND Col2 = ? AND Col3 = ? AND Col4 = ? AND Col5 = ?";
$hcSTMT = mysqli_prepare($connection, $hardCodedSTMT);
mysqli_stmt_bind_param($hcSTMT, "isiis", $var1, $var2, $var3, $var4, $var5);
mysqli_stmt_execute($hcSTMT);
//etc.
How to make that dynamic in the sense that $var2 thru $var5 may be null?
This is what I was thinking, but obviously there's a problem with the $variables and how to get them into a string (I think) instead of pointing to their actual values.
$buildSTMT = "SELECT Col1, Col2, Col3, Col4, Col5 FROM TABLE1 WHERE Col1 = ?"; //$var1 will not be null.
$parmTypes = "i";
$parmVariables = "$var1"; //should this be an array instead of string, or var by reference or? I dont want the value of $var1, but litterally the variable identified as $var1
if ($var2 != "") { //possible null
$buildSTMT .= "AND Col2 = ?";
$parmTypes .= "s";
$parmVariables .= "$var2" //how to add the variable and not it's value??
}
if ($var3 != "") { //possible null
$buildSTMT .= "AND Col3 = ?";
$parmTypes .= "i";
$parmVariables .= "$var3" //how to add the variable and not it's value??
}
if ($var4 != "") { //possible null
$buildSTMT .= "AND Col4 = ?";
$parmTypes .= "i";
$parmVariables .= "$var4" //how to add the variable and not it's value??
}
if ($var5 != "") { //possible null
$buildSTMT .= "AND Col5 = ?";
$parmTypes .= "s";
$parmVariables .= "$var5" //how to add the variable and not it's value??
}
//**** Prepare the full query statement. ****//
$stmt = mysqli_prepare($connection, $buildSTMT);
//**** Bind the parameters to the statement ****//
mysqli_stmt_bind_param($stmt, $parmTypes, $parmVariables); //$parmVariables needs to be $var1, $var2, $var3, $var4, $var5 (ex. if all 5 present).
//**** Run the statement ****//
mysqli_stmt_execute($stmt);
//**** Bind the results ****//
mysqli_stmt_bind_result($stmt, $resultCol1, $resultCol2, $resultCol3, $resultCol4, $resultCol5);
while (mysqli_stmt_fetch($stmt)) {
//do something with results
}
mysqli_stmt_close($stmt);
mysqli_close($connection);
I plan to make all those if statements and assignments in them, into a function. Also, will convert to OO later. As of now, all other code is still in procedural format.
For now, I'm wondering how to get the "mixed" vars (& not their values) into the last part of mysqli_stmt_bind_param ? 😕
