How to assign the correct type to bind_param dynamically based on retrieved column types?

Background info:

I am creating a class which will handle certain queries for me. One of these queries would be a SELECT query, where the user can select something based on a clause. I successfully retrieved the TYPES of the columns, but have no idea on how to actually decide based on the TYPE of the column which TYPE should be given to bind_param() (the first parameter).

My original plan was to cheap out and simply hard-code which argument should be given if the retrieved column type is a certain type.

Example of my original idea:

if ($type === "varchar") {
    $aVariable = "s";
}
//OR USE A SWITCH, BUT YOU GET THE IDEA
bind_param($aVariable, $someOtherVar);

However this isn’t something I am satisfied with because of numerous different reasons. So my question is:

Question: How can I use the column TYPES (if it’s possible) to determine whether the argument given to bind_param() should be one of the following types: “s, i, d, b”, without having to hard-code that the retrieved type = a certain letter.

Answer

Guessing the parameter type always smells. Whereas setting all parameters to “s” works flawless most of time.

So let me suggest you another solution. Make the types explicit but optional. By default it will be “s” but with a possibility to define the types manually, just like I did in my mysqli helper function:

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;
}

when you don’t need any sprecific types (most of time), just leave them out:

$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id > ?";
$res = prepared_query($conn, $sql, [1])->get_result();

but every time you need it, it’s already here and explicit, so you could set the exact type you want:

$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id > ?";
$res = prepared_query($conn, $sql, [1], "i")->get_result();

simple, clean and concise