How to make this SQL request with this particularity?

Question

In this search when the array returns the 4 as the SQL demonstrated below, I need the system to return all the results whose number of dorms and >= 4. You can help me get this result.

I converted the column to INT. It’s ugly in the DB, I didn’t make it, I was ready.

# Selecionando conforme quantidade de dormitorios
$dormi = $_POST['dorm'];
if (isset($_POST['dorm']) && is_array($_POST['dorm']) && count($_POST['dorm']) > 0){

    $where .= " DORMITORIO IN (". implode(', ', $dormi).") AND ";
    if (in_array(4, $_POST['dorm'])){

        $where .= trim($where,' AND ').' OR DORMITORIO >= 4 AND ';

    }

}

This is the SQL record of this search:

SELECT * FROM immovable WHERE 1=1 AND DORMITORIO IN (1, 2, 3, 4)

Answer 1

You need to do a routine that manages:

SELECT * FROM imovel WHERE dormitorio >= 4

instead of IN. It doesn’t make any sense to use IN in this case.

You can set the form options to 1,2,3 and 4, and use one array in this format to generate the query :

$opcoes = Array( '>= 0', '= 1', '= 2', '= 3', '>= 4' );

so, just do:

$dormi = 0 + @$_POST['dorm']; // A supressão é aceitável neste contexto
$dormi = $dormi>4 ? 4 : $dormi;
$opcoes = Array( '>= 0', '= 1', '= 2', '= 3', '>= 4' );

$where .= ' DORMITORIO '.$opcoes[$dormi];

and the results for 0, 1, 2, 3 and 4 shall be respectively:

WHERE DORMITORIO >= 0
WHERE DORMITORIO = 1
WHERE DORMITORIO = 2
WHERE DORMITORIO = 3 
WHERE DORMITORIO >= 4

It’s still a technical repair ¹, then suggest optimizing for your practical case.

_{^{1. Gambiarra}}

Answer 2

You can do it like this:

if (isset($_POST['dorm']) && is_array($_POST['dorm']) && count($_POST['dorm']) > 0){

   if (in_array('4', $_POST['dorm'])) {

      $where .= " ( DORMITORIO IN ('".implode("', '", $_POST['dorm'])."') OR DORMITORIO > 4 ) AND ";

   } else $where .= " DORMITORIO IN ('".implode("', '", $_POST['dorm'])."') AND ";

}

So even if the user selects the options 2, 4, the return will be consistent:

SELECT * FROM immovable WHERE 1=1 AND DORMITORIO IN ('2','4') OR DORMITORIO > 4

But in case you wanted to when selecting the option 4, show only the records with dorms greater than or equal to 4, do so:

if (isset($_POST['dorm']) && is_array($_POST['dorm']) && count($_POST['dorm']) > 0){

   if (in_array('4', $_POST['dorm'])) {
      $where .= ' DORMITORIO >= 4 AND ';
   } else {
      $where .= " DORMITORIO IN ('".implode("', '", $_POST['dorm'])."') AND ";
   }

}

Thus, if the user selects the options 2, 4, the return will be:

SELECT * FROM immovable WHERE 1=1 AND DORMITORIO >= 4

_{Note: I recommend using a class that provides options for creating dynamic filters, like this.}