I have been trying this is several ways, currently I have a mess
MySQL 4.1.1
PHP as the interface
I have a table of with a date range called seasons.
in it I have two date ranges and an amount to be charged
for each day in the range
2004-01-01 00:00:00 2004-06-01 00:00:00 44
2004-06-02 00:00:00 2004-10-31 00:00:00 110
seasonDateFrom seasonDateTo seasonRateWeekly
2004-06-02 00:00:00 2004-10-31 00:00:00 42.86
2004-01-01 00:00:00 2004-06-01 00:00:00 34.29
When I take a booking I have yet another range
$bookingDateFrom and $BookingDateTo
I need to get the SUM(seasonRateWeekly) for each day in the booking range.
Currently , and here is the bad bit, I can get it to work if I calculate
the number of days in the booking range, then loop through them in php
with foreach and increment a counter
SELECT seasonRateWeekly FROM seasons WHERE DATE_ADD('{$newbookingDateFrom}', INTERVAL
$i DAY)
BETWEEN seasonDateFrom AND seasonDateTo
This of course is almost useless as it takes 40 queries for 40 days. Not efficient at
all.
But I need the individual values, I think, to be able to query across season ranges
should
a booking range span two, or more, seasons.
As always, any help greatfully recieved
Kind regards
Kevin
--
______
(_____ \
_____) ) ____ ____ ____ ____
| ____/ / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \____) \_||_| \____) \____)
Kevin Waterson
Port Macquarie, Australia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
