How to use PDO bindParam in the IN () of the query? [duplicate]

Question

How to use PDO bindParam in the IN () of the query? [duplicate]

Navigation

#1 by (4 votes)
#2 by (2 votes)

2

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 it keeps sending me 0 rows.

Doing this in the other way, everything happens normally:

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

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

php pdo

asked by anonymous 13.04.2015 / 15:44

2 answers

use PHP to know if sent email has been opened Effect of filling a line

score 4 · Answer 1

One suggestion would be to format your query string and then make bindParam for each parameter, eg

    $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 scroll through the array and make% 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();

score 2 · Answer 2

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

One way to perform dynamic bind is to know the number of the parameters, create a string with n queries, and concatenate them in the query within the IN clause. The values must 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);