How do I do a correct check?

Lennart Ericson
Lennart Ericson used Ask the Experts™
on
Experts and Gurus, I am making a booking application for a small parking place. It has five places. Customers can book one car for one 24-hour period up to seven 24-hour periods.
Apart from registering the name, register plate information etc pertaining to the parker, I have a data base table like this:

id , parking_space_nr, arrival_day, departure_day, arrival_code, departure_code, reference_nr, 24_hrs_periods

Arrival_code and departure_code is UNIX time for arrival_day and departure_day respectively.

There is a grace period built in:
$code_of_arrival = strtotime($_POST['day_of_arrival']) + 46800;
$code_of_departure = ($code_of_arrival + ($_POST['24_hrs_periods'] * 86400) - 3600);

If I need to join the two data base tables it is done on reference_nr.

I have tried to construct a piece of code to find out whether a certain parking_space_nr is free or not the day a customer wants to book:
$SQL = "SELECT * FROM platser WHERE arrival_code between '$code_of_arrival' AND '$code_of_departure' AND parking_space_nr = '1' ";
$ret = mysql_query($SQL);
if (!$ret) { echo("[0] ".mysql_error()); }
else {
$number_of_bookings = mysql_num_rows($ret);
}
if($number_of_bookings > 0) {  the particular parking_space_nr is not free and a parking prohibited sign is shown for that parking_space_nr }
else  { a parking allowed sign is shown  }

I would like to ask you gurus and experts how to construct the piece of code to correctly find out whether the parking space is free or not.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kash2nd Line Engineer

Commented:
why not have another field that is boolean 0/1. You have it as 0 when parking space is free and 1 when not. have that field join with nr field to do the search for whether the space is free or not. Just giving you a logic on making it easy for yourself.

let me know how it goes.

Author

Commented:
I thought that is what I am doing,  but not with another field.
Kash2nd Line Engineer

Commented:
adding another field would definitely help I suppose because then when you query the database then you are just looking for the value of that field instead of doing joins etc(if any).
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
OK, could you give me an example what you mean?
Top Expert 2012

Commented:
I don't think a simple column addition is applicable here because the presence of a row in the platser table already denotes that the particular parking space is booked for the specified time.  You need something that will check the following scenarios

1. booked_arrival < queried_arrival and booked_departure > queried_arrival (e.g.. booked reservation is from may 10-15 and query is for may 11-13)
2. booked_arrival > queried_arrival and booked_departure > queried_arrival and booked_arrival < queried_departure (e.g. booked reservation is from may 11-15 and query is for may 10-13.  You need the third test because may 11-15 and may 18-20 also satisfy the first two but do not denote an overlap)
3. booked_arrival < queried_arrival and booked_departure < queried_departure and booked_departure > queried_arrival (e.g. booked reservation is from may 11-13 and query is for may 12-15.  Again, 3 tests are needed)

So your SQL should be something like:
SELECT count(id) as bookings FROM platser WHERE
(arrival_code < '$code_of_arrival' AND departure_code > '$code_of_departure'
OR
arrival_code > '$code_of_arrival' AND departure_code > '$code_of_arrival' AND arrival_code < '$code_of_departure'
OR
arrival_code < '$code_of_arrival' AND departure_code < '$code_of_departure' AND
departure_code > '$code_of_arrival'
) AND parking_space_nr = '1'

NOTE:
1. untested so I may have missed something, but I think you should already get the picture on the scenarios you need to test.  
2.  Note that I replaced "SELECT * " with a "SELECT count(id) as bookings " - this is so that you only have to read the single returned field which is more efficient than returning all columns when the only thing you want to do is to count the matching rows
Kash2nd Line Engineer

Commented:
i stand corrected. the person above has clarified it better than i have.
Top Expert 2012

Commented:
Just realized there's one more case to consider:

4. booked_arrival > queried_arrival and booked_departure < queried_arrival (case 1 in reverse)

Also, each scenario should be enclosed in parentheses so:

SELECT count(id) as bookings FROM platser WHERE 
( 
 (arrival_code < '$code_of_arrival' AND departure_code > '$code_of_departure' )
 OR 
 (arrival_code > '$code_of_arrival' AND departure_code > '$code_of_arrival' AND arrival_code < '$code_of_departure' )
 OR
 (arrival_code < '$code_of_arrival' AND departure_code < '$code_of_departure' AND departure_code > '$code_of_arrival')
 OR
  ( arrival_code > '$code_of_arrival' AND departure_code < '$code_of_departure' )
) AND parking_space_nr = '1'

Open in new window


Let me know if it works for you.  Thanks.

Author

Commented:
The above code gives me all parking spaces are taken. If I change "SELECT count(id) as bookings FROM" to "SELECT * FROM" improves a bit, but is still not 100% correct. I might have misunderstood what your intension of using "SELECT count(id) as bookings". I'd appreciate it if yoou could explain.
Top Expert 2012

Commented:
Well, in your original post you did a "select * from" but you seem to have done nothing to it but only count the rows.  Doing a "select * from" just to count the rows is wasteful, that's why I just suggested "select count(id)" to just get the count.  But if you really plan to process / display the contents of the row, then "select * from" is fine (though selecting only the specific columns you really need is better).

To move forward, can you supply sample data (prefer a dump of the table) and a sample desired output?

Author

Commented:
A dump of the table:
CREATE TABLE `platser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parking_space_nr` int(11) DEFAULT NULL,
  `arrival_day` date DEFAULT NULL,
  `arrival_code` int(11) DEFAULT NULL,
  `departure_day` date DEFAULT NULL,
  `departure_code` int(11) DEFAULT NULL,
  `24_hrs_periods` int(11) DEFAULT NULL,
  `reference_nr` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `platser` VALUES (1,5,'2012-06-01',1338548400,'2012-06-02',1338631200,1,201204219);
INSERT INTO `platser` VALUES (2,4,'2012-06-02',1338634800,'2012-06-03',1338717600,1,201204220);
INSERT INTO `platser` VALUES (3,3,'2012-06-03',1338721200,'2012-06-04',1338804000,1,201204221);
INSERT INTO `platser` VALUES (4,2,'2012-06-01',1338548400,'2012-06-02',1338631200,1,201204222);
INSERT INTO `platser` VALUES (5,1,'2012-06-02',1338634800,'2012-06-03',1338717600,1,201204223);

The two latest records represent a booking of the same day and length as the first two.

The system is expected to show parking_space_nr 4 and 5 to be taken, but it shows they are not taken.
Top Expert 2012
Commented:
Okay I did miss another thing - I should have used >= and <= instead of > and <.
SELECT * FROM platser WHERE 
( 
 (arrival_code <= 1338548400 AND departure_code >= 1338631200 )
 OR 
 (arrival_code >= 1338548400 AND departure_code >= 1338548400 AND arrival_code <= 1338631200 )
 OR
 (arrival_code <= 1338548400 AND departure_code <= 1338631200 AND departure_code >= 1338548400)
 OR
  ( arrival_code >= 1338548400 AND departure_code <= 1338631200 )
) AND parking_space_nr = '2'

Open in new window


Created the table and imported data.  Now to test for bookings that overlap time range 1338548400 to 1338631200, then for 1338634800 to 1338717600:
mysql> SELECT * FROM platser WHERE 
    -> ( 
    ->  (arrival_code <= 1338548400 AND departure_code >= 1338631200 )
    ->  OR 
    ->  (arrival_code >= 1338548400 AND departure_code >= 1338548400 AND arrival_code <= 1338631200 )
    ->  OR
    ->  (arrival_code <= 1338548400 AND departure_code <= 1338631200 AND departure_code >= 1338548400)
    ->  OR
    ->   ( arrival_code >= 1338548400 AND departure_code <= 1338631200 )
    -> );
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
| id | parking_space_nr | arrival_day | arrival_code | departure_day | departure_code | 24_hrs_periods | reference_nr |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
|  1 |                5 | 2012-06-01  |   1338548400 | 2012-06-02    |     1338631200 |              1 |    201204219 |
|  4 |                2 | 2012-06-01  |   1338548400 | 2012-06-02    |     1338631200 |              1 |    201204222 |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM platser WHERE 
    -> ( 
    ->  (arrival_code <= 1338634800 AND departure_code >= 1338717600 )
    ->  OR 
    ->  (arrival_code >= 1338634800 AND departure_code >= 1338634800 AND arrival_code <= 1338717600 )
    ->  OR
    ->  (arrival_code <= 1338634800 AND departure_code <= 1338717600 AND departure_code >= 1338634800)
    ->  OR
    ->   ( arrival_code >= 1338634800 AND departure_code <= 1338717600 )
    -> );
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
| id | parking_space_nr | arrival_day | arrival_code | departure_day | departure_code | 24_hrs_periods | reference_nr |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
|  2 |                4 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204220 |
|  5 |                1 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204223 |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
2 rows in set (0.00 sec)

Open in new window


Note that I did not include the "AND parking_space_nr=N" clause to see all bookings that overlap the requested arrival and departure times.  Just put it back if you need to check for a specific space.

Playing with it a bit to use values that are not  equal to the ones on the table:
mysql> SELECT * FROM platser WHERE 
    -> ( 
    ->  (arrival_code <= 1338634000 AND departure_code >= 1338717900 )
    ->  OR 
    ->  (arrival_code >= 1338634000 AND departure_code >= 1338634000 AND arrival_code <= 1338717900 )
    ->  OR
    ->  (arrival_code <= 1338634000 AND departure_code <= 1338717900 AND departure_code >= 1338634000)
    ->  OR
    ->   ( arrival_code >= 1338634000 AND departure_code <= 1338717900 )
    -> );
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
| id | parking_space_nr | arrival_day | arrival_code | departure_day | departure_code | 24_hrs_periods | reference_nr |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
|  2 |                4 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204220 |
|  5 |                1 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204223 |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM platser WHERE 
    -> ( 
    ->  (arrival_code <= 1338634000 AND departure_code >= 1338721000 )
    ->  OR 
    ->  (arrival_code >= 1338634000 AND departure_code >= 1338634000 AND arrival_code <= 1338721000 )
    ->  OR
    ->  (arrival_code <= 1338634000 AND departure_code <= 1338721000 AND departure_code >= 1338634000)
    ->  OR
    ->   ( arrival_code >= 1338634000 AND departure_code <= 1338721000 )
    -> );
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
| id | parking_space_nr | arrival_day | arrival_code | departure_day | departure_code | 24_hrs_periods | reference_nr |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
|  2 |                4 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204220 |
|  5 |                1 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204223 |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM platser WHERE 
    -> ( 
    ->  (arrival_code <= 1338634000 AND departure_code >= 1338721900 )
    ->  OR 
    ->  (arrival_code >= 1338634000 AND departure_code >= 1338634000 AND arrival_code <= 1338721900 )
    ->  OR
    ->  (arrival_code <= 1338634000 AND departure_code <= 1338721900 AND departure_code >= 1338634000)
    ->  OR
    ->   ( arrival_code >= 1338634000 AND departure_code <= 1338721900 )
    -> );
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
| id | parking_space_nr | arrival_day | arrival_code | departure_day | departure_code | 24_hrs_periods | reference_nr |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
|  2 |                4 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204220 |
|  3 |                3 | 2012-06-03  |   1338721200 | 2012-06-04    |     1338804000 |              1 |    201204221 |
|  5 |                1 | 2012-06-02  |   1338634800 | 2012-06-03    |     1338717600 |              1 |    201204223 |
+----+------------------+-------------+--------------+---------------+----------------+----------------+--------------+
3 rows in set (0.00 sec)

Open in new window


So far it looks correct (unless I'm missing something again which won't be a surprise given my insomnia - it's almost 4am here).  But if you agree, do make tests with the other different scenarios.

Author

Commented:
Thanks! I will test. Have a good sleep now!

Author

Commented:
I have tested and it looks good. Thanks so much for your help! It is invaluable.
Top Expert 2012

Commented:
You're welcome..... zzzzzz

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial