1

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
Chirka
  • 31
  • 4
  • Not sure if duplicate of https://stackoverflow.com/questions/51138463/mysqli-stmtbind-param-specify-another-data-type-than-s-for-each-paramete – Nigel Ren Mar 06 '21 at 14:51
  • 1
    It's not a duplicate because of the statement "It would make the code smaller". But, smaller code is not always better code! – Luuk Mar 06 '21 at 14:52
  • 2
    In reality I think you will find that `bind_param()` does not actually care about the data type of the types i.e. `'ssss'` would probably work just fine. But is it good practice? Thats a little more difficult – RiggsFolly Mar 06 '21 at 15:30
  • Yes, you definitely can. There's pretty much no reason to bind as anything else than string. – Dharman Mar 06 '21 at 18:06

1 Answers1

1

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135