php - How to dynamically bind params in mysqli prepared statement?

Question

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, but mysqli_stmt_bind_param can't handle arrays. As a solution, I tried making a foreach 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 a foreach to the mysqli_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 1

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]);