Forum Moderators: open
PLEASE, DON'T PANNIC about the explanation...
It's a little bit complicated so here is an example of what i'm trying to do (it's not a real example just a stripped part to make it more clearly [indeed i still find it difficult]):
/*=========================================================/
[BOF]: TABLES
------------------------
disposable
------------------------
id_disposable (key) [values: 1,2,3,4]
name_disposable [values: low,medium,high,complete]
------------------------
date_has_disposable //table filled with value pairs of day-month for a year
------------------------//(it must be able to deal with february 29 when needed!)
id_day (key) [values: from 1 to 31]
id_month (key) [values: from 1 to 12]
id_disposable [values: from 1 to 4]
------------------------
rent_has_date_disposable
------------------------
id_rent_has_date_disposable(key)[values: from 1 to 6]
id_disposable(key)[values: from 1 to 4]
min_days(key)[values: any integer]
max_days(key)[values: any integer]
day_price[values: any float]
------------------------
rent
------------------------
id_room (key) [values: any integer]
id_rent_has_date_disposable[values: from 1 to 6][EOF]: TABLES
/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/
[BOF]: EXPLANATION AND EXAMPLES
==========================================
Example of table rent_has_date_disposable
==========================================
1,1,3,6,136
1,1,7,20,120
1,1,21,30,100
1,2,3,6,140
1,2,7,20,130
.../...
2,1,3,6,150
2,1,7,20,140
.../...
Explaining it (it's difficult to explain but let's try it):
- We select a room. (look at rent table).
Supose that the rooms are classified as "suite, room with views, small room, big room, etc."
These room classes are represented by an id (id_rent_has_date_disposable from rent_has_date_disposable table).
Every class has diferent disponibility indicated by days of year (date_has_disposable)
and diferent day prices indicated by the number of days it will be rented.
- Then we select some days from (only) one year.
- Then we must look the disposable for EVERY selected day (look at date_has_dispoble table).
- Every class of room has a different price based on the number of days it will rent.
So if we selected 4 days we look at min - max values to retrieve the price for EVERY day
(remember that every day could have a different disponibility (date_has_disposable table))
so it could have a different price.So we have:
room (id_room): 1
room class (id_rent_has_date_disposable): 1
selected days (m/d/y) & [disponibility (id_disposable)]:
01/01/2000 [low]
02/29/2000 [medium]
03/20/2000 [high]
n selected days (between min_days and max_days): 3
prices (day_price):
[low] [from 3 to 6 days] = 136
[medium] [from 3 to 6 days] = 150
[high] [from 3 to 6 days] = 170[EOF]: EXPLANATION AND EXAMPLES
/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/I'm completely lost. The problem is deal with the dates.
I consulted mysql reference and tought that the WHILE statement maybe could help to deal with
multiple dates but it seems only to work with stored procedures.
I thinked that maybe using DAY_OF_YEAR (1 to 366) instead of DAY-MONTH pairs could be easier
but then we crash with february 29, so we cannot use a unique and permanent day id.
So i thinked to use DAY_OF_YEAR combined with some IF (february 29...) statement.
But i still don't know which is the best way to go.
========================================================*/
If it's too complicated to explain, then it's too complicated to debug.
Wipe out 90% of the code.
Start with one function. then add another. then another.
You'll be able to figure out what works and what doesn't.
To be honest, I couldn't decipher your code/explanation.
I was conscious of the difficulty to understand the explanation but I reached a point where I was a little desperate.
Now things are easier. Forget about the previos post.
SELECT rent_to_season.ID_SEASON, DAY_PRICE
FROM rent, rent_to_season, season_rent
WHERE PLACES = '$places'
AND rent.ID_RENT_TO_SEASON = rent_to_season.ID_RENT_TO_SEASON
AND '$count_days' BETWEEN MIN_DAYS AND MAX_DAYS
AND DAY_ID = DAY('$selected_dates')
AND MONTH_ID = MONTH('$selected_dates')
AND rent_to_season.ID_SEASON = season_rent.ID_SEASON
This works fine for me if $selected_dates represents only one date. The problem is that $selected_dates is a PHP array that can contain multiple dates (2006-03-03,2006-04-27). So i need to obtain the price for every DAY-MONTH value pairs in $selected_dates.
(It can be done by searching the price for individual days or grouping the days with the same ID_RENT_TO_SEASON)
$sql = "
SELECT
rent_to_season.ID_SEASON,
PRICE_DAY
FROM rent,
season_rent,
rent_to_season
WHERE
PLACES = '$places'
AND rent.ID_RENT_TO_SEASON = rent_to_season.ID_RENT_TO_SEASON
AND season_rent.ID_SEASON = rent_to_season.ID_SEASON
AND '$count_days' BETWEEN MIN_DAYS AND MAX_DAYS
";//Concat day and month values from database to allow comparision
$sql .= " AND CONCAT(DAY_ID, ',' , MONTH_ID) in (";//initialize a counter for loop
$counter = 1;//loop trough the selected dates
foreach($selected_dates as $key => $value){
//Concat day and month values from user selection to allow comparision
$sql .= " CONCAT( DAY('". $value ."') , ',' , MONTH('". $value ."') )";//if the counter is lower than total days add a 'comma' because there are more dates to compare
if($counter < count($selected_dates)){
$sql .= ", ";
}
//if we are in the last date (the last item in the array) close the '... in(...)' sentence
else {
$sql .= ")";
}
//increment counter
$counter++;
}//append the last condition for the SQL sentence
$sql .=" AND rent_to_season.ID_SEASON = season_rent.ID_SEASON";
