How to make this SQL request with this particularity?

Question

How to make this SQL request with this particularity?

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

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

I converted the column to INT . It was ugly the thing in DB , I did not do it, it 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 resutaldo of this search:

SELECT * FROM property WHERE 1 = 1 AND BEDROOM IN (1, 2, 3, 4)

php mysql sql

asked by anonymous 07.10.2014 / 20:36

2 answers

3

You need to do a routine that manages:

SELECT * FROM imovel WHERE dormitorio >= 4

instead of IN . It makes no sense to use IN in this case.

You can set form options to 1,2,3 and 4, and use 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 will be respectively:

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

It's still a technical 1 , so I suggest optimizing for your practical case.

1. Gambiarra

07.10.2014 / 20:40

Selecting fields that are repeated in the MySql database Google Maps with file_get_contents error. What can it be?

score 1 · Accepted Answer

You can do 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 property WHERE 1 = 1 AND BEDROOM IN ('2', '4') OR BEDROOM > 4

But if you wanted to select the 4 option, just show the records with dormitories 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 ";
   }

}

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

SELECT * FROM property WHERE 1 = 1 AND BEDROOM> = 4

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