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?

PHP Code:
//$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.
PHP Code:
$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 ?