Link to home
Create AccountLog in
Avatar of Lennart Ericson
Lennart EricsonFlag for Sweden

asked on

How do I do a correct check?

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.
Avatar of Kash
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Lennart Ericson


I thought that is what I am doing,  but not with another field.
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).
OK, could you give me an example what you mean?
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'
arrival_code > '$code_of_arrival' AND departure_code > '$code_of_arrival' AND arrival_code < '$code_of_departure'
arrival_code < '$code_of_arrival' AND departure_code < '$code_of_departure' AND
departure_code > '$code_of_arrival'
) AND parking_space_nr = '1'

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
i stand corrected. the person above has clarified it better than i have.
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' )
 (arrival_code > '$code_of_arrival' AND departure_code > '$code_of_arrival' AND arrival_code < '$code_of_departure' )
 (arrival_code < '$code_of_arrival' AND departure_code < '$code_of_departure' AND departure_code > '$code_of_arrival')
  ( 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.
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.
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?
A dump of the table:
CREATE TABLE `platser` (
  `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`)

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.
Avatar of johanntagle
Flag of Philippines image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks! I will test. Have a good sleep now!
I have tested and it looks good. Thanks so much for your help! It is invaluable.
You're welcome..... zzzzzz