I am working on a parking booking project and want to ensure that no spot is overbooked for a particular time period. I am using the following syntax, but it is not working for me,
SELECT * FROM reservation WHERE
`spot_id`='$id' AND
`Location`='$location' AND
(`in` >= '$datein' OR `out` <= '$dateout' ) AND
(`in` < '$datein' OR `out` > '$dateout')
Please advise me.
I got the correct syntax as follows:
SELECT * FROM reservation WHERE spot_id='$id' AND Location='$location' AND (`in` between '$datein' AND '$dateout' OR `out` between '$datein' AND '$dateout' OR `in` <= '$datein' AND `out` >= '$dateout')
Firstly, your logic is flawed.
After or equal to date $datein or before or equal to date $dateout signifies any date, ever.
(`in` >= '$datein' OR `out` <= '$dateout' )
And before $datein or after $dateout just confuses it further.
(`in` < '$datein' OR `out` > '$dateout')
Even so, what you want to do will not work the way you are currently attempting it. What if a spot was booked for a month and you want to check the middle week?
You need to normalise your data. E.g. An entry in a table for every individual day booked for each spot and query that table accordingly.