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)
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
