How could you get around the problem:
Create an auxiliary table with the rental dates of the apartment called leasing dates. Then instead of putting the dates in a single field, refer to the given by the apartment id, and bring with you the dates relating 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'
)
);
Done that, 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 consultation would bring 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 this just join 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)
}
Exit:
Apt: ap test
Lease period(s): 12/08/2015 - 01/09/2015 - 02/09/2015
You have dates separated by commas in the field
data_ja_locado?– Jorge B.
I don’t see a good way to do that...
– Jorge B.
@Jorgeb. That’s right!
– caiocafardo
That’s terrible!
– Jorge B.
I imagine I would have to know all the dates inside the checkin and checkout and see if there is any of that date in the field data_ja_locado, but I do not know how
– caiocafardo
I know what you want to do, the problem is that the camp
data_ja_locadois not a date is a string with dates, there is no way to do so a search, that I know. You’ll have to pick up all the dates and take care of it in your PHP.– Jorge B.
@Jorgeb. and what is the correct form?
– caiocafardo
The right way was to have a table
apartamento_dataswith all dates per apartment. For example, with the fieldsid | id_apartamento | data_ocupacao.– Jorge B.
The date has to be in American format: 2015-08-10 AND 2015-08-11 and be a DATE field, if you are using a VARCHAR field, or TEXT, you need to fix this.
– Ivan Ferrer
You’re doing it wrong, instead of putting the dates in a single comma-separated field. Place them in a separate table, in American format and call them through a foreign key.
– Ivan Ferrer