How to set up a query with optional conditions?

Question

How to set up a query with optional conditions?

Navigation

#1 by (4 votes)

3

In the example:

SELECT * FROM exemplo WHERE ID in(2, 3, 4)

It does if you have these ID in the bank.

PHP:

$id= implode(', ', $_GET['id']);
$nome= implode(', ', $_GET['nome']);
$cidade= implode(', ', $_GET['cidade']);

$query = mysql_query("SELECT * FROM EXEMPLO WHERE id IN($id) AND nome IN($nome) AND cidade IN($cidade)");

//Se tiver de acordo com a condição traz a linha dentro da condição, se não traz todas as linhas.

I've tried this, but I do not know if it's correct;

$id= "(id='".implode("' OR id='", $_GET['id'])."') AND";
$nome= "(nome='".implode("' OR nome='", $_GET['nome'])."') AND";
$cidade= "(cidade='".implode("' OR cidade='", $_GET['cidade'])."')";

$query = mysql_query("SELECT * FROM EXEMPLO WHERE $id $nome $cidade");

php mysql sql array query

asked by anonymous 04.08.2015 / 17:51

1 answer

The total value appears Doubt with the use of placeholder and onfocus in HTML

score 4 · Accepted Answer

Better solve in PHP and analyze whether the array is empty or not. If it is empty, bring everything, if it is not, use the condition. After the various changes in the wording, according to the comments I would do something like this:

$query = "SELECT * FROM exemplo WHERE TRUE";
if (!empty($condicao1)) {
    $lista = implode(",", $condicao1);
    $query .= " AND ID NOT IN ($lista)";
}
if (!empty($condicao2)) {
    $lista = implode(",", $condicao2);
    $query .= " AND NOME NOT IN ($lista)";
}
if (!empty($condicao3)) {
    $lista = implode(",", $condicao3);
    $query .= " AND CIDADE NOT IN ($lista)";
}

I put echo just to illustrate, use it on the PDO as you see fit. Of course, if I had the code, I would know the real situation, it could help more, but this is abstractly. In concrete situation I would refine it.

This technique can be used to mount any type of condition.