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

user_10869719 0
Viewed 53

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.

mysqli sql php
2 Answers

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

user_2855870

Your code should never try to guess the type. You should bind everything as string. 99.99% of the time it will make no difference. There are some edge cases when the actual type make a difference, but in these situations you can hardcode the type when you know what the type should be. If you guess the type then you will only add more bugs into your software.

All the data you receive from HTML form is of type string. MySQL does the type casting dynamically depending on the context. Why should you bother with casting the value in PHP? Simply send everything as is to MySQL and let it decide what should the type be.

user_18394390
Related