I've a table that stores the reservations of rooms and for each room I want select the reservations, by a PHP date. Here is it the query:
SELECT id_prenotazione, id_alloggio, FROM_UNIXTIME(data_checkin,'%d/%m/%Y') AS checkin,
FROM_UNIXTIME(data_checkout,'%d/%m/%Y') AS checkout, pagata, esclusiva
FROM prenotazioni
WHERE id_alloggio = $id_alloggio
AND FROM_UNIXTIME(data_checkin,'%d/%m/%Y') >= ".$data."
OR FROM_UNIXTIME(data_checkout,'%d/%m/%Y') <= " .$data.")
ORDER BY checkin ASC
LIMIT 1;
but if there are two or more reservations on the same room, the query always returns the first reservation, while if I change "ASC" with "DESC", returns only the last reservation. I want the single reservation of a specific room ($id_allogio) in a specific date($data). What am I doing wrong? Thanks in advance for the answer.
Why not using the between statement :
SELECT col1,col2,col3,col4,blabla
FROM prenotazioni
WHERE
id_alloggio = $id_alloggio
and FROM_UNIXTIME(data_checkin,'%d/%m/%Y') between date_1 AND date_2
ORDER BY checkin ASC
LIMIT 1;
Your question makes it difficult to tell exactly what you need. It seems that you need to see the single reservation matching a particular room on a particular day.
But you don't tell us what should happen if there are multiple reservations for a room on a day. That is, you don't tell us what should happen if there is an overbooking.
At any rate, your scheme for handling your TIMESTAMP (that is, your UNIXTIME) data items is not very good.
I suggest you revise your query as follows:
SELECT id_prenotazione,
id_alloggio,
FROM_UNIXTIME(data_checkin,'%d/%m/%Y') AS checkin,
FROM_UNIXTIME(data_checkout,'%d/%m/%Y') AS checkout,
pagata,
esclusiva
FROM prenotazioni
WHERE id_alloggio = $id_alloggio
AND STR_TO_DATE($data, '%d/%m/%Y') >= data_checkin
AND STR_TO_DATE($data, '%d/%m/%Y') < data_checkout + INTERVAL 1 DAY
ORDER BY checkin ASC
What did I change here?
First, I converted your presentation style date, which represents 17-Dec-2013 as 17/12/2013, to an internal MySQL native date format, before using it for comparison using inequalities like >= and <. It's necessary to do that. When you compare strings like 17/12/2013 and 18/11/2013 you get incorrect ordering.
Additionally, the way I wrote the query allows MySQL to use indexes to satisfy it.
Second, I changed your checkout comparison to
parameter-date < data_checkout + INTERVAL 1 DAY
That's because you seem to be using data types for data_checkin and data_checkout that include both calendar dates and times. The comparison I used allows the result to be correct when data_checkout shows a checkout time other than midnight.
Finally, I got rid of the LIMIT 1. It seems to me that you need to know if there's more than one reservation for a room on a particular date.
I solved the problem this way:
I rewrote the query on the information you have given me:
SELECT id_prenotazione,id_alloggio, data_checkin, data_checkout, pagata, esclusiva FROM prenotazioni WHERE id_alloggio = $id_alloggio AND '$data' >= data_checkin AND '$data' < data_checkout + INTERVAL 1 DAY
With these corrections, the query rertuns the single reservation of specific room in a specific date. Thanks for Your useful answer!