How do I pass dynamic parameters in a preparedStatment?

Question

I have a function to enter data into the database dynamically. It worked well when running the query directly using only the function query(), once I started to restructure the function to work with preparedStatments, it stopped working. I simply cannot pass these parameters in the method bind_param since the values are dynamic.

<?php

$mysqli = new mysqli('localhost','root','','loja_teste');

function insert($tabela, $args = []){
    global $mysqli;

    $campos = implode(', ', array_keys($args));
    $valores = array_values($args);

    # achar os tipos, dinamicamente;
    $type = $binds = "";
    $i = 1;
    foreach($valores as $valor){
        switch(gettype($valor)){
            case 'integer':
                $type .= 'i';
            break;
            case 'string';
                $type .= 's';
            break;
            default:
                $type .= 's';
            break;    
        }    
        $binds .= "?";
        if($i < count($valores)){
            $binds .= ", ";        
        }
        $i++;
    }

    $sql = "INSERT INTO {$tabela} ({$campos}) VALUES ({$binds})";
    if($prepare = $mysqli->prepare($sql)){
                # Aqui onde retorna o erro
        if($prepare->bind_param($type, implode(',', $valores))){
            $prepare->execute();    
        } else {
            die($mysqli->error);
        }    
    } else {
        die($mysqli->error);    
    }
    
    
}

var_dump(insert('tabela', ['nome'=>'Anel de Ouro','preco'=>1000]));

?>

With the function above, it returns:

Strict Standards: Only variables should be passed by Ference in...

Warning: mysqli_stmt::bind_param(): Number of Elements in type Definition string doesn’t match number of bind variables in...

But if I do it this way:

$vals = implode(',', $valores);    
if($prepare->bind_param($type, &$vals)){
   $prepare->execute();
}

Returns:

Fatal error: Call-time pass-by-Ference has been Removed in...

Someone knows how to fix this ?

Answer 1

I solved the problem by reordering the values, and calling the function bind_param through another function called cal_user_func_array as @rray said up there, I just couldn’t quite understand it, so I did different using the same function.

first I separated the part that returned the values depending on the type, and put everything in a function called type:

function type($args = [], $bind = false){
    # Essa função retorna o codigo para o valor 
    # Se o $bind for igual a true, ela vai retornar os simbolos
    $type = $binds = "";    
    $i = 1;
    foreach($args as $valor){
        switch(gettype($valor)){
            case 'integer':
            $type .= 'i';
            break;
            case 'string';
            $type .= 's';
            break;
            default:
            $type .= 's';
            break;  
        }   
        $binds .= "?";
        if($i < count($args)){
            $binds .= ", ";     
        }
        $i++;
    }
    if($bind){
        return $binds;  
    }
    return $type;
}

It returns the value types. If the second argument is like true it returns the symbols for the query SQL.

var_dump(type(['nome'=>'Anel de Ouro','preco'=>1000]));

string(2) "si"

var_dump(type(['nome'=>'Anel de Ouro','preco'=>1000], true));

string(4) "?, ?"

2nd I took care of the parameters, creating a new function parametros:

function parametros($args=[], $sec=false){
    # Essa função retorna os parametros já referenciados
    # Se a variavel $sec for igual a true, ela vai retornar  os campos separados por vírgulas   
    $type = type($args);    
    $parametro[] = &$type;
    foreach($args as $key=>$valor){
        $parametro[] =& $args[$key];    
    }   
    if($sec){
        $campos = implode(', ', array_keys($args)); 
        return $campos; 
    }
    return $parametro;
}

It returns different data depending on the second argument as well:

var_dump(parametros(['nome'=>'Anel de Ouro','preco'=>1000]));

array(3) { [0]=> string(2) "si" [1]=> string(12) "Gold Ring" [2]=> int(1000) }

var_dump(parametros(['nome'=>'Anel de Ouro','preco'=>1000], true));

string(11) "name, price"

3º I modified my original function by adding the function cal_user_func_array:

$mysqli = new mysqli('localhost','root','','loja_teste');

function insert($tabela, $args = []){
    global $mysqli;

    $campos = parametros($args, true); # aqui passo os nomes dos campos da tabela
    $binds = type($args, true); # aqui passo os simbolos (?) para a consulta

    $sql = "INSERT INTO {$tabela} ({$campos}) VALUES ({$binds})";
    if($prepare = $mysqli->prepare($sql)){
        if(call_user_func_array(array($prepare, "bind_param"), parametros($args))){
            if($prepare->execute()){
                print "Cadastrado com sucesso"; 
            }
        } else {
            die("Erro (execute): " . $mysqli->error);
        }
    } else {
        die("Erro: (prepare)" . $mysqli->error);    
    }


}

Then I ran this function, using the same data:

var_dump(insert('tabela', ['nome'=>'Anel de Ouro','preco'=>1000]));

Successfully registered

Answer 2

The function to identify the field type is only showing integer and string, I made some changes but I couldn’t identify the blob, Then I made a mooring in condition string:

function type($args = [], $bind = false){
    # Essa função retorna o codigo para o valor 
    # Se o $bind for igual a true, ela vai retornar os simbolos
    $type = $binds = "";    
    $i = 1;
    foreach($args as $valor){
        switch(gettype($valor)){
            case 'integer':
                $type .= 'i';
                break;
            case 'double':
                $type .= 'd';
                break;
            case 'string':
                if (array_search($valor, $args) == 'fd_imagem') {
                    $type .= 'b';
                } else {
                    $type .= 's';
                }
                break;
            default:
                $type .= 's';
                break;  
        }   
        $binds .= "?";
        if($i < count($args)){
            $binds .= ", ";     
        }
        $i++;
    }
    if($bind){
        return $binds;  
    }
    return $type;
}