Re: New to PHP & mySQL

Ray, Why not just simply give them a list of available times that meet their number of days requested starting with the day they ask for. Would look something like this. HTH. Dan. Have a great day. ---------------------------------- <? $req_start_date = '2002-04-01'; $req_end_date = '2002-04-20'; $limit = 10; // limit how many are listed $req_start = strtotime($req_start_date); $req_end = strtotime($req_end_date); $num_days = ceil(($req_end - $req_start) / 86400); <? $req_start_date = '2002-04-01'; $req_end_date = '2002-04-20'; $limit = 10; $req_start = strtotime($req_start_date); $req_end = strtotime($req_end_date); $num_days = ceil(($req_end - $req_start) / 86400); echo "$req_start $req_end $num_days<BR>"; $sql = " SELECT DATE_ADD(prev_end.bookin_start, INTERVAL 1 DAY) as available_start, DATE_SUB(next_beg.bookin_end, INTERVAL 1 DAY) as available_end, intervening_book.booking_start as intervening_date FROM bookings as prev_end LEFT JOIN bookings as next_beg ON next_beg.villa_id = '$req_villa_id' WHERE prev_end.villa_id = '$req_villa_id' AND (TODAYS(prev_end.booking_end) - TODAYS(next_beg.booking_start) >= $num_days OR pre_end.booking_end is null) LEFT JOIN bookings as intervening_book ON intervening_book.villa_id = '$req_villa_id' AND ( intervening_book.booking_end > prev_end.booking_end AND intervening_book.bookeng_start < next_start.booking_start) WHERE prev_end.villa_id = '$req_villa_id' AND intervening_date is null ORDER BY availabel_start LIMIT $limit "; ?> ---------------------------------------------- Ray Healy ) wrote:

Hi all (details of database at then end of this message) I hope someone can give me some advice. I am trying to create a database and access via PHP for a friend of mine that has a caravan park. What I want him to be able to do is to add bookings for the caravans via a PHP page and for clients to be able to search to see if a caravan is available for rent. I have created 2 tables and have put data into it via command prompt and also retrieved the data from it and carried out a join linking the 2 tables together. Which all seems to work well. The one thing I cannot get into my head is how can you tell the database that all the days between the "start" and "end" dates are booked. Also when people search for a caravan on a specific date and say they want it for 7 day the database/PHP checks to see if the entire period is totally free for them and does not colide with another booking. I am not sure whether I should be in list list of the PHP list so sorry if I have got it wrong. Any advice or places to visit would be greatly appreciated. Thanks for all your help Ray Details of what i have done already: mysql> use matrix Database changed mysql>CREATE TABLE bookings ( -> booking_id SMALLINT (6) NOT NULL AUTO_INCREMENT, -> booking_start DATE NOT NULL DEFAULT '0000-00-00', -> booking_end DATE NOT NULL DEFAULT '0000-00-00', -> villa_id SMALLINT (6) NOT NULL DEFAULT '0', -> PRIMARY KEY (booking_id) -> ); mysql>INSERT INTO bookings VALUES (1, '2002-04-01', '2002-04-15', 3); mysql>INSERT INTO bookings VALUES (2, '2002-03-23', '2002-04-04', 1); mysql>CREATE TABLE villas ( -> villa_id SMALLINT (6) NOT NULL AUTO_INCREMENT, -> vill_name VARCHAR (25) NOT NULL DEFAULT '', -> PRIMARY KEY (villa_id) -> ); mysql>INSERT INTO villas VALUES (1, 'Gandy'); mysql>INSERT INTO villas VALUES (2, 'Hathaway'); mysql>INSERT INTO villas VALUES (3, 'Healy'); mysql>INSERT INTO villas VALUES (4, 'Mcleod'); mysql> SELECT * FROM bookings; +------------+---------------+-------------+-----------------+ | booking_id | booking_start | booking_end | villa_id | +------------+---------------+-------------+-----------------+

|          1      | 2002-04-01      | 2002-04-15      |        3   |
|          2      | 2002-03-23      | 2002-04-04      |        1   |

+------------+---------------+-------------+-----------------+ 2 rows in set (0.17 sec) mysql> SELECT * FROM villas; +----------+------------+ | villa_id | villa_name | +----------+------------+

|        1    | Gandy        |
|        2    | Hathaway  |
|        3    | Healy         |
|        4    | Mcleod      |

+----------+------------+ 4 rows in set (0.00 sec) mysql> SELECT villa_name, booking_start, booking_end FROM bookings LEFT JOIN villas ON bookings.villa_id = villas.villa_id; +------------+---------------+--------------------+ | villa_name | booking_start | booking_end | +------------+---------------+--------------------+

| Healy       | 2002-04-01     | 2002-04-15      |
| Gandy      | 2002-03-23     | 2002-04-04      |

+------------+---------------+--------------------+ 2 rows in set (0.00 sec)