How do I not select records that are within a date?

Question

How do I not select records that are within a date?

Navigation

#1 by (1 votes)

0

I have a table in MySQL with apartment, like this:

id |   nome   | data_ja_locado
1  | ap teste | 09/08/2015, 10/08/2015, 11/08/2015

Then when the user does a search he wants to find apartments that are available within a space of time. If it put as CHECKIN 08/08/2015 and as CHECKOUT 11/08/2015 was not to appear the "ap test" of the example above.

How to make this query?

I tried this way:

SELECT * FROM apartamento WHERE data_locado NOT BETWEEN '10/08/2015' AND '11/08/2015'

But he lists the apartment anyway, can anyone give me a light?

mysql date select

asked by anonymous 21.07.2015 / 17:55

1 answer

Fix name in search field Calling from 2 post in the wordpress loop

score 1 · Accepted Answer

How could you get around the problem:
Create an auxiliary table with the apartment rental dates called location_dates . Then, instead of putting the date in a single field, check the data for the apartment id, and bring the dates related to this table:

+------------------------------------+
| id_data | id_apto | data_ja_locado |
+------------------------------------+
|    1    |    1    |  2015-08-09    |
+------------------------------------+
|    2    |    1    |  2015-08-10    |
+------------------------------------+
|    3    |    1    |  2015-08-11    |
+------------------------------------+
|    4    |    1    |  2015-08-12    |
+------------------------------------+
|    5    |    1    |  2015-09-01    |
+------------------------------------+
|    6    |    1    |  2015-09-02    |
+------------------------------------+

First you will bring the list of apartments that are not part of your query:

$sql = "SELECT apt.* FROM apartamento apt
        INNER JOIN datas_locacao loc
        ON(loc.id_apto=apt.id_apto)
        WHERE loc.data_ja_locado 
        NOT BETWEEN '2015-08-09' AND '2015-08-11' ";

Where the query will display the other results, as in the example:

$aptos = array(
              0 => array(
                        'id_apto' => 1,
                        'nome'=> 'ap teste'
                        )
             );

Once you have done this, now inside the foreach , make a second query bringing all the dates for this query:

$sql_datas = "SELECT id_data,
                     DATE_FORMAT(data_ja_locado,'%d-%m-%Y') as data_loc
              FROM   datas_locacao
              WHERE  id_apto = '$id_apto' and data_ja_locado 
              NOT    BETWEEN '2015-08-09' AND '2015-08-11' 
              ORDER  BY data_ja_locado ASC ";

This query would have the following result:

+-------------------------+
| id_data |   data_loc    |
+-------------------------+
|    4    |  12/08/2015   |
+-------------------------+
|    5    |  01/09/2015   |
+-------------------------+
|    6    |  02/09/2015   |
+-------------------------+

Where the query will display the other results, as in the example:

$datas_locacao['id_apto']['1'] = array(
                                0 => array(
                                      'id_data' => 4,
                                      'data_loc'=> '12/08/2015'
                                     ),
                                1 => array(
                                      'id_data' => 2,
                                      'data_loc'=> '01/09/2015'
                                     ),
                                2 => array( 
                                      'id_data' => 3,
                                      'data_loc'=> '02/09/2015'
                                     )
                               );

Done, just add and display the results:

foreach ($aptos as $key => $apto) {
          $datas = array();
          foreach ($datas_locacao[$key][$apto['id_apto']] as $data) {
              $datas[] =  $data['data_loc'];         
          }
   echo "<b>Apto: <b/>" . $apto['nome'];
   echo "<b>Período(s) de locação:<b/>" . implode(" - ", $datas)    
}

Output:

Apt: ap test
Rental period (s): 12/08/2015 - 01/09/2015 - 09/02/2015 p>