CODEWITHSUNDEEP
phpmysqlsqldatabasedatetime
MariusNV
MariusNV

Reputation: 320

Check time interval from database with PHP

I have a database called booking_system which has a table named bookings with the following structure:

+--+--------+----+----------+----------+----------+
|id|username|room|book_date |time_since|time_until|
+--+--------+----+----------+----------+----------+
|1 |mari80u6|3   |2013-11-17|09:00:00  |11:00:00  |
+--+--------+----+----------+----------+----------+

The data is inserted into the database using PHP (no issue there). The problem is that now, based on the book_date, time_since and time_until information from the database, i need to check $current_date which is equal to date('Y-m-d', strtotime('now')) against the book_date column from the database in the first place. Secondly I want to check $current_hour which is equal to date('H:i:s') if it is between the interval of time_since & time_until columns. Finally if 

mysql_num_rows == 0

the src attribute of an image should change to green

<img id="room3" src="css/images/3Green.png">

meaning that the room is available for booking otherwise it should look be red 

<img id="room3" src="css/images/3Red.png">

I really don't know how to properly write the sql query being stuck at this:

$sql="SELECT id FROM bookings WHERE room='3' AND time_since < '$current_hour' < time_until";

Also if there are other prefered methods to achieve what I am looking for please let me know, I really appreciate any help I can get and I hope I made myself clear enough.

Upvotes: 0

Views: 846

Answers (1)

John Conde
John Conde

Reputation: 219864

value < value < value is not a valid syntax. You need to use AND with multiple comparisions instead:

SELECT id 
FROM bookings 
WHERE room='3' 
AND time_since < '$current_hour' 
AND '$current_hour' < time_until";

Or use BETWEEN:

SELECT id 
FROM bookings 
WHERE room='3' 
AND '$current_hour' BETWEEN time_since AND time_until";

Upvotes: 1

Related Questions