如何在两个日期之间选择一条记录

Question

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.

Answer 1

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;