Avatar of Lennart Ericson
Lennart Ericson
Flag 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.
Web DevelopmentMySQL ServerPHP

Avatar of undefined
Last Comment
johanntagle

8/22/2022 - Mon
Kash

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

ASKER
I thought that is what I am doing,  but not with another field.
Kash

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).
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Lennart Ericson

ASKER
OK, could you give me an example what you mean?
johanntagle

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
Kash

i stand corrected. the person above has clarified it better than i have.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

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

ASKER
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.
johanntagle

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Lennart Ericson

ASKER
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.
ASKER CERTIFIED SOLUTION
johanntagle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lennart Ericson

ASKER
Thanks! I will test. Have a good sleep now!
Lennart Ericson

ASKER
I have tested and it looks good. Thanks so much for your help! It is invaluable.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johanntagle

You're welcome..... zzzzzz