I need help creating a method for dynamic mysqli prepared statements. The code below has errors. I know I'm completely off with the way mysqli_stmt_bind_param is set up but I can't figure out a solution. My coding style is probably strange since I'm using OO php, but procedural mysqli. I haven't had the time to figure out OO mysqli since the books and videos I read/watch use procedural mysqli. Most of the solutions I've seen for this problem use OO mysqli. I'd prefer to get a short-term fix for this rather than having to learn PDO after I spent so much time learning mysqli.
public function create($sql, $param_type, $param){
//param_type should be set as $param_type = "'ssss'" so that single quotes get passed into the variable
//param should be an array
//param array items should be escaped
$stmt = mysqli_prepare($this->dbc, $sql);
mysqli_stmt_bind_param($stmt, $param_type, join(array_values($param), ", "));
$result = mysqli_stmt_execute($stmt);
if($result){
return true;
} else{
return false;
}
mysqli_stmt_close($stmt);
}
To use OO mysqli is simple:
mysqli_blah($this->dbc) call to $this->dbc->blah().mysqli_stmt_blah($stmt) call to $stmt->blah().Also, always check the return value from prepare() and execute(). They return false when there's an error in parsing or execution, and you need to check for these and report errors every time.
The mysqli_stmt_bind_param() function is tricky because it expects a variable number of arguments, one for each letter in the param type argument, not a string of comma-separated values. Also, it requires you pass variables by reference, not scalars, and not a single array.
WRONG: mysqli_stmt_bind_param($stmt, "sss", "red,green,blue");
WRONG: mysqli_stmt_bind_param($stmt, "sss", "red", "green", "blue");
WRONG: mysqli_stmt_bind_param($stmt, "sss", $param_array);
RIGHT: mysqli_stmt_bind_param($stmt, "sss", $param1, $param2, $param3);
This makes it difficult and confusing to do what you're doing: writing a general-purpose function to prepare and execute an SQL statement with a dynamic number of parameters. You have to use call_user_func_array() but you have to rewrite the array of arguments as an array of references.
I wrote examples in a couple of my past SO answers:
PDO solves this much more easily, you don't have bind anything, you just pass an array to execute().