php - How to dynamically bind params in mysqli prepared statement?
Get the solution ↓↓↓I'm trying to create a function for my project. I would like it to handle all the check functions. What I mean with that is before you start inserting a row in your database you check if a row exists with that email address.
To use this function dynamically it needs to be flexible. So I did put my variables in an array, butmysqli_stmt_bind_param
can't handle arrays. As a solution, I tried making aforeach
loop.
The query:
$sql = "SELECT users_id, users_email FROM users WHERE users_id = ? AND users_email = ?;";
Calling the function:
check_database($sql, array($id, $mail), array("s", "s"), $location);
My original function:
function check_database($sql, $variables, $types, $location)
{
require $_SERVER['DOCUMENT_ROOT'] . '/includes/db/db.inc.php';
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
header("Location: " . $location . "?error=sqlerror");
exit();
} else {
mysqli_stmt_bind_param($stmt, $types, $variables);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (!$row = mysqli_fetch_assoc($result)) {
return true;
}
}
}
I added aforeach
to themysqli_stmt_bind_param
like this:
foreach ($types as $index => $type) {
mysqli_stmt_bind_param($stmt, $type, $variables[$index]);
}
This gives me an error and I don't know how to solve it :(
Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
Answer
Solution:
You are on a very right track! Such a function should be an everyday companion for the every PHP programmer using mysqli, but strangely, only few ever have an idea of creating one.
I've had an exactly the same idea once and implemented a mysqli helper function of my own:
function prepared_query($mysqli, $sql, $params, $types = "")
{
$types = $types ?: str_repeat("s", count($params));
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
return $stmt;
}
Main differences from your approach
- the connection is made only once. Your code connects every time it executes a query and this is absolutely NOT what it should do
- it can be used for any query, not only SELECT. You can check the versatility of the function in the examples section down in the article
- types made optional as most of time you don't care for the type
- no bizarre $location variable. Honestly, whatever HTTP stuff should never be a part of a database operation! If you're curious how to properly deal with errors, here is my other article Error reporting in PHP
With your example query it could be used like this
$check = prepared_query($sql, [$id, $mail])->get_result()->fetch_row();
or, if you want a distinct function, you can make it as well
function check_database($mysqli, $sql, $params, $types = "")
{
return prepared_query($mysqli, $sql, $params, $types)->get_result()->fetch_row();
}
and now it can be called as
$check = check_database($sql, [$id, $mail]);
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: the browser (or proxy) sent a request that this server could not understand.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.