Search with Mysql PDO in Array

Asked

Viewed 324 times

5

I have that 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 well, but if the $whereBusca content is more than a region, it doesn’t give the search, I don’t know what I’m missing, look at the example of when it doesn’t 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
  • 1

    See if quotation marks change anything: array("whereBusca" => "'".implode("','", $whereBusca)."'" )

  • Hi, also not.... until I understood what you thought, to be like this right? " 'Taim-Bibi','Jardin'" ...

  • In fact it was the only thing I thought, to give explode and then implode. I do not know if in this case PREPARE is the best way to solve the problem. I’ve been posting some answers on the site that make a more complex WHERE, with a LIKE '%' for each term, maybe it’s nice you take a peek. (maybe it’s too much). The ideal would be to give a print_r in strategic places.

  • See if any help, there are some 3 similar versions lost here: http://answall.com/search?q=user%3A70+Where+like

  • It may be that the PDO is sanitizing the quotes (most likely). It would be the case to mount the string manually even. The problem is that you would have to avoid sql Injection on your own, in case.

  • Vo already leave in the question with the quotation marks of the way you spoke, which I think is the "correct", as it would be this manual form?

  • You cannot pass 1 placeholder and N values, need to be in the same ratio or be 1 placeholder for 1 value. How to use PDO bindParam in query IN()?

  • This field has several values separated by commas ?

  • What fields? From the database?

  • Apparently that’s what you said, thanks, now I fight here to make the loop for each query value... Ta dureza!

  • And if you do so: 'SELECT id, name, path FROM regiao WHERE PATH IN ("Itaim-Bibi","gardens")'

  • Managed to hit the in() what’s the problem now?

  • @rray is that I am not the biggest expert in the world, I try to use the example that you passed, but ta crap....

  • $whereBusca is an array right? and the number of elements may vary

  • This, from 1 to 400 (400 regions in the database), comes as pq string picked via GET, but turns into array with explode

Show 10 more comments

2 answers

3


Cannot set 1 placeholder for N values, the ratio must be the same or 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 play 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 queries ;)

  • Perfect! Only exchange there the variable $interrogation by placeholders. Thank you very much!

  • @caiocafardo, thanks for the correction :D had not noticed.

  • 1

    @Bacco, great suggestion I’ll add her, another way I had thought was implode(',', array_fill(0, $n, '?')); ... seems more expensive.

-2

And if you do so: 

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

Then I wouldn’t need the blast and the implode

  • Is that using PDO, and this value "Itaim-Bibi", "gardens" is just an example. The search can be done for 1 region or 400, depending on the user...

Browser other questions tagged php mysql pdo

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