phpmysqliprepared-statementbindparam

Can I use string ("sss...") for all the values in bind_param() type? If no, why not?


I am making a method in my class, where the parameters for the method are $sql, $types, $values .

function getResult($sql, $types, $values){
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param( "$types" , ...$values);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        return $result;
    } else{
        return "There is no such row";
    }
}

But i wonder, maybe i could make a function where $types are automatically generated based on the count of $values and give it a string ("s"). Something like this:

function getResult($sql, $values){
    $stmt = $this->conn->prepare($sql);
    $types = str_repeat("s", count($values));
    $stmt->bind_param( $types, ...$values);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
        return $result;
    } else{
        return "There is no such row";
    }
}

Is it bad practise? It would make the code smaller


Solution

  • Yes, you absolutely can use strings to bind every single parameter. Binding parameters as strings works 99.99% of the time. There are only a handful of cases in MySQL where the type of the parameter matters.

    What you can do is create a function that takes $types as an optional argument. That would be the best practice because it leaves you with an option to specify types if you really need them.

    function getResult(string $sql, array $values, ?string $types = null): ?mysqli_result
    {
        $stmt = $this->conn->prepare($sql);
        if (is_null($types)) {
            $types = str_repeat("s", count($values));
        }
        $stmt->bind_param($types, ...$values);
        $stmt->execute();
        return  $stmt->get_result() ?: null;
    }
    

    P.S. It's a bad idea to have the function return two types of values. Type hint your functions and stick to a single type.