How to use PDO bindParam in query IN()?

Asked

Viewed 477 times

3

I have a query similar to this:

$modelos = "1,2,3,4";
$sql = "Select codigo_modelo, nome From modelos Where codigo_modelo in (:codigo_modelo)"                
$sql->bindParam(":codigo_modelo",$modelos,PDO::PARAM_INT); //TAMBEM JA TENTEI COM PARAM_STR

But keeps returning me 0 Rows.

Doing otherwise everything happens normally:

$modelos = "1,2,3,4";
$sql = "Select codigo_modelo, nome From modelos Where codigo_modelo in (".$modelos.")"

Any idea? I would like to continue using the bindParam for obvious reasons...

php pdo

  • For each value a placeholder is required, you can implement this mechanism thus

2 answers

4

A suggestion would be to format your query string and then do the bindParam for each parameter, example:

    $modelos = explode(",","1,2,3,4"); // transforma string em array
    $query = sprintf("Select codigo_modelo, nome From modelos Where codigo_modelo in (%s)",
            implode(
            ',',
            array_map(
                function() {
                   static $x = 0;
                   return ':cod_'.$x++;
                },$modelos
            )
        )
    );

This will format your query string as follows: 

Select codigo_modelo, nome From modelos Where codigo_modelo in (:cod_0,:cod_1,:cod_2,:cod_3)

So you can go through the array and do bindParam of the parameters

    $sql = $conexao->prepare($query);
    for ($i = 0; $i < sizeof($modelos); $i++){
        $sql->bindParam(":cod_".$i,$modelos[$i]); //$sql->bindParam(":cod_0",1) ...;
    }
    $sql->execute();

4

For each value in the query it is necessary to inform the respective named/placholder, how you passed the value(1,2,3,4) PDO understood this as a single value represented by :cod_modelo.

One way to perform dynamic bind is to know the parameter number, create a string with n interrogations and concatenate them in the query within the IN. Values should be passed in execute() as an array and no longer individually with bindParam()

$dados = array(1,2,3,4);
$totalInterrogacoes = count($dados);

$interrogacoes = str_repeat('?,', $totalInterrogacoes);
$interrogacoes = substr($interrogacoes, 0, -1); // remove a última virgula

$cmd = "SELECT * FROM modelos WHERE codigo_modelo in($interrogacoes)";
$stmt = $sql->prepare($cmd);
$stmt->execute($dados);

$operacao->execute($values);

Browser other questions tagged php pdo

You are not signed in. Login or sign up in order to post.