Search with MySQL PDO in Array

Question

Search with MySQL PDO in Array

Navigation

#1 by (3 votes)
#2 by (-1 votes)

4

I have this code:

$whereBusca = "itaim-bibi";
$whereBusca  = explode(',', $whereBusca);
$sql = $pdo->prepare('SELECT id, nome, caminho FROM regiao WHERE caminho IN (:whereBusca)');
$sql->execute(array("whereBusca" => "'".implode("','", $whereBusca)."'"));
$resultadoSql = $sql->fetchAll();
foreach ($resultadoSql as $valorSql) {
    echo utf8_encode($valorSql['id']);
}

It works fine, but if the content of $ whereBusca is more than a region, it does not search, I do not know what I'm missing, look at the example of when it does not work:

$whereBusca = "itaim-bibi,jardins";
$whereBusca  = explode(',', $whereBusca);
$sql = $pdo->prepare('SELECT id, nome, caminho FROM regiao WHERE caminho IN (:whereBusca)');
$sql->execute(array("whereBusca" => "'".implode("','", $whereBusca)."'"));
$resultadoSql = $sql->fetchAll();
foreach ($resultadoSql as $valorSql) {
    echo utf8_encode($valorSql['id']);
}

php mysql pdo

asked by anonymous 18.12.2015 / 14:11

2 answers

-1

And if you do this:

'SELECT id, nome, caminho FROM regiao WHERE caminho IN ("itaim-bibi","jardins")'

Then I would not need the explode and implode

18.12.2015 / 14:54

Two conditions in SQL query with Laravel 4 Angular JS - Get the value sent by the select of a directive

score 3 · Accepted Answer

It is not possible to define 1 placeholder for N values, the ratio must be the same as 1 placeholder for 1 value.

To make a IN with a number of dynamic parameters count the element number of the array, generate a string with the right number of placeholders then I threw this in the query and pass an array to execute()

$valores = ['itaim-bibi','jardins', 'Doge island'];
$n = count($valores);

$placeholders = '?'. str_repeat(',?', $n - 1); //no exemplo a string gerada é ?,?,?

$consulta = "SELECT id, nome, caminho FROM regiao WHERE caminho IN ($placeholders)";
$sql = $pdo->prepare($consulta);
$sql->execute($valores);

The generated SQL is something like:

SELECT id, nome, caminho FROM regiao WHERE caminho IN (?,?,?)

3 elements in the array, 3 questions;)