To do a complex search I would have to use OR to combine all the instructions?

Question

I would like a help to make SQL as per the data in this figure.

What I know is that if I send AND for all characteristics and one of them return false or erro all SQL will be compromised.

I thought I’d do it with OR. Is it right? But if I do, one of the characteristics can be ignored, no?

Follow the code I have so far

# Essa variável pega os tipos enviados por $_GET e separá-os.
$tipos  = (@explode('/', implode('/', $_GET['tipo'])));

# Esse foreach pega cada variável e monta a instrução
# **CATEGORIA LIKE '%{$word}% OR**
foreach($tipos as $word){
    $sql[] = "CATEGORIA LIKE '%{$word}%'";
}

# Então SQL pega o resultado do foreach e colocar no WHERE
# e eu termino de ordenar os resultados.
$sql    = 'SELECT * FROM imovel WHERE ' .implode(' OR ', $sql);
$sql   .= ' ORDER BY IMO_CODIGO DESC LIMIT '.$inicio. ', '. $limite;

Answer 1

Assembling a filter

ATTENTION: Always validate your variables before sending them to the database.

Validating the filter

If values are standard, use a code instead of a word in search, so you’ll always have consistent results.

$where = 'WHERE 1=1 AND ';

$tipo = Array(
  1, // Apartamento
  2, // Casa
  3  // Terreno
);

if (isset($tipo) && !empty($tipo)){

    // se $tipo for um array e for maior que 0
    if (is_array($tipo) && count($tipo) > 0) {
       $where .= " tipo IN ('".implode("','",$tipo)."') AND ";
    } else {
       $where .= " tipo = '{$tipo}' AND ";
    }

}

$dormitorio = Array(2,3,4); // Quantidade de dormitorios

if (isset($dormitorio) && !empty($dormitorio)){

   if (is_array($dormitorio) && count($dormitorio) > 0){
     $where .= " dormitorio IN ('".implode("', '", $dormitorio)."') AND ";

   } else {
     $where .= " dormitorio = '{$dormitorio}' AND ";
   }
}

_{I recommend sending the filter via POST, but can also be done via GET, no problem. Always remember to avoid SQL Injection, here a question on the subject.}

_{Validate the received data to check which fields will enter the query as done in the above example (if (isset($tipo) && !empty($tipo)){, if (isset($dormitorio) && !empty($dormitorio)){, if (isset($dormitorio) && !empty($dormitorio)){), it is good to do this with the variables received from the form ($_POST, $_GET).}

---

Searching for a Keyword

And if the user can do a keyword search.

$palavrachave = 'Sacada';

if (isset($palavrachave) && !empty($palavrachave)){
  // Adicione todos os campos que possa ser buscado como palavra chave
  $where .= "(nome LIKE '%{$palavrachave}%' OR descricao LIKE '%{$palavrachave}%' OR cidade LIKE '%{$palavrachave}%') AND "; 
}

_{Add all fields that contain text content to the keyword query if you use this in your filter. Mount a block between parentheses using OR, so that all results that have the term in any of the fields, is brought in the result, and concatenates with the rest of the filter with AND.}

---

Table-riding

$sql = "SELECT * FROM imoveis ".trim($where,' AND ');

_{Put a AND after each query field and when adding the fields in the query remove only the last one using a trim. And even if there is no filter, there will be no problems as we start the WHERE searching for all results where 1=1 be true, ie all. And the AND of that expression will be removed ($where = 'WHERE 1=1 AND ';), resulting in WHERE 1=1.}

---

Upshot

All information provided:

SELECT * FROM imoveis WHERE 1=1 AND  tipo IN ('1','2','3') AND  dormitorio IN ('2', '3', '4') AND (nome LIKE '%Sacada%' OR descricao LIKE '%Sacada%' OR cidade LIKE '%Sacada%')

_{The search will bring all the data that the field type has the value 1, 2 or 3, informed in the variable $tipo, 2, 3 or 4 in the field dormitorio, informed in the variable $dormitorio, and having the password Sacada name, description or city, entered in the variable $palavrachave.}

Without the field tipo informed:

SELECT * FROM imoveis WHERE 1=1 AND  dormitorio IN ('2', '3', '4') AND (nome LIKE '%Sacada%' OR descricao LIKE '%Sacada%' OR cidade LIKE '%Sacada%')

_{The query will return all data that has the value 2, 3 or 4 in the field dormitorio, informed in the variable $dormitorio, and hold the keyword in the name, description or city.}

No field informed:

SELECT * FROM imoveis WHERE 1=1

_{The query will return all table data.}

Answer 2

You must use and and or separated into blocks with parentheses, for example:

(tipo = apartamento or tipo = casa) and 
(dormitórios = 2 or dormitórios = 3) and 
(vagas = 1 or vagas = 2) and 
(area > 60 and area < 120) and 
(valor > 100000 and valor < 150000)

Sorry the quality of the answer, I’m on mobile, as soon as possible edit and improve...