How to mount query in PDO with items before concatenated

Question

How to mount query in PDO with items before concatenated

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

The question is as follows, in mysql we can concatenate a query and then perform the search, as follows:

$cidades = '&& (cidade = 100 || cidade = 101 || cidade = 102)';
$order = 'id ASC';

$sql = 'SELECT * FROM tabela WHERE status = 1 '.$cidades.' ORDER BY '.$order;

The variable $cidade can be empty or have n search elements, I can not do this, I do not know if it would be this way:

$sql = $this->_db->prepare('SELECT * FROM tabela WHERE status = 1 :cidades ORDER BY :order');

$sql->execute(array(':cidades' => $cidades, ':order' => $order));

php sql pdo query

asked by anonymous 13.10.2015 / 22:45

2 answers

0

What you can do is to put all city ids in an array if you have an id, make an implode, otherwise leave the variable blank.

Example:

We have the ids of cities: 26,50 and 20.

$cidades =($arrayCidade != '' && count($arrayCidade) > 0) ? "AND (id='". implode("' OR id='", $arrayCidade) . "')" : '';

Only use within the PDO the variable $ cities. Make sure you are entering only numbers in the array, so you do not have SqlInjection.

13.10.2015 / 23:04

Resize image and remove borders Error when querying postgres table using Jpa, Hibernate and Postgres

score 0 · Accepted Answer

In order to perform this dynamic query you will need some adjustment, the first is to transform $cidades into an array and play in a IN() class, change execute() to bindValue() to make order by work, the bind can not be done with the column name just the index.

$in = "";

$posicao = 2;
if(!empty($cidades)){
    $cidades = array(100, 101, 102);
    $totalInterrogacoes = count($cidades);
    $interrogacoes = str_repeat('?,', $totalInterrogacoes);
    $interrogacoes = substr($interrogacoes, 0, -1);
    $in = " AND cidades IN($interrogacoes) ";
}   

$sql = "SELECT * FROM tabela WHERE status = ? "
$stmt = $this->_db->prepare($sql);
$stmt->bindValue(1, 1);

if($in){
    $stmt->bindValue($posicao, implode($cidades));
    $sql .= $in ." ORDER BY ?";
    $posicao++;
}   

$stmt->bindValue($posicao, 1, PDO::PARAM_INT);

$stmt->execute();