php - MySQLi dynamic prepared function with array binding

Question

I am attempting to create a database query function which can take multiple parameters and be reused elsewhere, however tried a number of methods online similar to my approach and they are not working as expected.

function query($query, $bindings, $type)

I want to be able to run queries on the go with this single function, this type of function is a lot easier with the PDO driver as you could simply enter the binding inside ->execute($binding); however in this case I am forced to use MySQLi as the application currrently relies on it but wanting to upgrade it to use prepared statements.

An example of how I need to be able to use the function to ensure it is reusable and flexible:

$engine->query("SELECT * FROM accounts WHERE email = :mail AND id = :id",array(':mail' => $_POST['mail'], ':id' => 2))->fetch_assoc();

Lets split each of them up. First is the statement, then the array which is used to bind the parameters used, then the types of the parameters, and finally the type of fetch_ to utilize on the query (ASSOC, OBJECT, ARRAY) etc.

"SELECT * FROM accounts WHERE email = :mail AND id = :id", array(':mail' => $_POST['mail'], ':id' => 2), "si" ->fetch_assoc();

Answer 1

though to implement named parameters would be quite a task, the rest is pretty doable.

A PHP >= 5.6 variant, implementing splat operator:

function query($query, $params = NULL, $types = NULL)
{
    if (!$params)
    {
        return $mysqli->query($query);
    }
    $statement = $this->mysqli->prepare($select);
    $types = $types ?: str_repeat('s', count($params));
    $statement->bind_param($types, ...$params);
    $statement->execute();
    return $statement->get_result();
}

used like this

$sql = "SELECT * FROM accounts WHERE email = ? AND id = ?";
$row = $engine->query($sql, [$_POST['mail'], 2])->fetch_assoc();

or, if you want to set types explicitly

$row = $engine->query($sql, [$_POST['mail'], 2], "si")->fetch_assoc();