How to mount PDO query with items previously concatenated

Question

The doubt is the following, 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 elements for search, I can not do so, I do not know also if it would be that way:

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

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

Answer 1

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

Example:

we have city ids: 26,50 and 20.

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

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

Answer 2

To be able to perform this dynamic query will, need some adjustment, the first is to transform $cidades in an array and play in a clasula IN(), trade in execute() for bindValue() to make order by work, bind cannot be done with column name.

$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();