Help and advice needed for code and desing
Moderator: General Moderators
Help and advice needed for code and desing
First I should say I'm a newbie in programming trying to learn php and mysql.
I made a form where a client choose an arrival date and a departure date.
I want to show the rates between arrival and departure date.I have 3 different rates period.
should I design the DB defining 366 rows 1 for each day and 2 columns: arrival derpature ?
I'm not sure how to work out when arrival and departure dates overlaps from one rate period to the other.
I need your advice .
Thanks in advance from the Dominican Republic
I made a form where a client choose an arrival date and a departure date.
I want to show the rates between arrival and departure date.I have 3 different rates period.
should I design the DB defining 366 rows 1 for each day and 2 columns: arrival derpature ?
I'm not sure how to work out when arrival and departure dates overlaps from one rate period to the other.
I need your advice .
Thanks in advance from the Dominican Republic
No I don't have fixed dates. the user should pick arrival and departure date and then a should show the rates that correspond for those dates.
I have 3 different rate periods
January to April
May to August
September to December
So if a user select arrival on april 20th and departure on may 05th that will overlap from one rate period to the other.
that's where I need the advice on how will be the better way to design the table for the rates.
Regards
I have 3 different rate periods
January to April
May to August
September to December
So if a user select arrival on april 20th and departure on may 05th that will overlap from one rate period to the other.
that's where I need the advice on how will be the better way to design the table for the rates.
Regards
A detailed example : The user select the following dates from a form:
Arrival date : April 20th 2005
Departure date : May 05th 2005
I should charge $20 in april and $ 30 in May.
is like a booking system. The problem is I'm a beguiner, I'm certainly not asking people how do I do this from beginning to end.........instead I was wondering if anyone can give me some helpful hints on how to get started.
Arrival date : April 20th 2005
Departure date : May 05th 2005
I should charge $20 in april and $ 30 in May.
is like a booking system. The problem is I'm a beguiner, I'm certainly not asking people how do I do this from beginning to end.........instead I was wondering if anyone can give me some helpful hints on how to get started.
Yes I Have a database looking like this:
Start date | end date | rate |
01-jan-05 | 30-apr-05 | $20
01-May-05 | 31-Aug-05 | $30
01-sep-05 | 31-dec-05 | $40
That's the way I have the Database (I don't know if that is the proper way or if I should have a row for every day of the year).
But at the end what I need is when a user choose a date for example:
Star date 29-apr-05
end date 03-may-05
I would like to be able to display each day rate like this:
29-apr-05 $20
30-apr-05 $20
01-may-05 $30
02-may-05 $30
Total $100
and that is the code that I have not been able to do.
I hope is clearer now
I'm sorry if I'm confusing you, maybe is because my english is not so good, I'm a Spanish speaking person. I apologise
Start date | end date | rate |
01-jan-05 | 30-apr-05 | $20
01-May-05 | 31-Aug-05 | $30
01-sep-05 | 31-dec-05 | $40
That's the way I have the Database (I don't know if that is the proper way or if I should have a row for every day of the year).
But at the end what I need is when a user choose a date for example:
Star date 29-apr-05
end date 03-may-05
I would like to be able to display each day rate like this:
29-apr-05 $20
30-apr-05 $20
01-may-05 $30
02-may-05 $30
Total $100
and that is the code that I have not been able to do.
I hope is clearer now
I'm sorry if I'm confusing you, maybe is because my english is not so good, I'm a Spanish speaking person. I apologise
-
Code for month related per day costs:
This part of code may be adapted to week or day based seasonal costs easily, no need of a database for this.
djot
-
Code for month related per day costs:
This part of code may be adapted to week or day based seasonal costs easily, no need of a database for this.
Code: Select all
<?php
$cost=0;
$total_costs=0;
$date_arrival=mktime(0, 0, 0, 4, 29, 2005); // 29th of April 2005
$date_departure=mktime(0, 0, 0, 5, 3, 2005); // 3rd of May 2005
//echo "\$date_arrival=".$date_arrival."<br>";
//echo "\$date_departure=".$date_departure."<br>";
//$difference=$date_departure-$date_arrival;
//echo "\$difference=".$difference."<br>";
//$days_of_vacation=$difference / (60*60*24);
//echo "\$days_of_vacation=".$days_of_vacation."<br>";
//echo "<hr>";
$date_end=$date_arrival;
while ($date_departure >= $date_end) {
$current_month = date('n', $date_end);
$current_day = date('j', $date_end);
echo "current month: ".$current_month."<br>";
echo "current day: ".$current_day."<br>";
switch($current_month) {
case 1: $cost=20; break;
case 2: $cost=20; break;
case 3: $cost=20; break;
case 4: $cost=20; break;
case 5: $cost=30; break;
case 6: $cost=30; break;
case 7: $cost=30; break;
case 8: $cost=30; break;
case 9: $cost=40; break;
case 10: $cost=40; break;
case 11: $cost=40; break;
case 12: $cost=40; break;
}
echo "cost per day this month: ".$cost."<hr>";
$total_costs+=$cost;
$date_end=$date_end+1+60*60*24;
}
echo "<hr><hr>";
echo "total_costs: ".$total_costs."<br>";
?>-
djot thank you for your time and effort.
the code you posted is working fine.
But still I have another concern:
What about if the rates periods are not in the same month and year ? I mean if a month have mor than one rate
Let's say that my info is the following:
Start date | end date | rate |
25-Dec-04 | 27-apr-05 | $20
28-apr-05 | 31-Aug-05 | $30
01-sep-05 | 24-dec-05 | $40
look at this example
Start date 25-apr-05
end date 01-may-05
How can I work around this part ?
the code you posted is working fine.
But still I have another concern:
What about if the rates periods are not in the same month and year ? I mean if a month have mor than one rate
Let's say that my info is the following:
Start date | end date | rate |
25-Dec-04 | 27-apr-05 | $20
28-apr-05 | 31-Aug-05 | $30
01-sep-05 | 24-dec-05 | $40
look at this example
Start date 25-apr-05
end date 01-may-05
How can I work around this part ?
I like the 366 days in the table more..
table rates(day, rate)
----------------------
01 20
02 20
03 20
...
037 30
038 30
...
150 20
...
Getting the total rate: (using the mysql with rollup modifier)
Update the rate for a given period:
table rates(day, rate)
----------------------
01 20
02 20
03 20
...
037 30
038 30
...
150 20
...
Getting the total rate: (using the mysql with rollup modifier)
Code: Select all
SELECT day, sum(rate) AS rate
FROM rates
WHERE day BETWEEN DAYOFYEAR($arrival) AND (DAYOFYEAR($departure)+1)
GROUP BY day WITH ROLLUPCode: Select all
UPDATE rates
SET rate=45
WHERE day BETWEEN DAYOFYEAR($begin) AND (DAYOFYEAR($end)+1)
Last edited by timvw on Thu Jun 30, 2005 5:19 pm, edited 1 time in total.
Djot please have a closer look at the second example I posted you will notice that I changed the start and the end dates of the prices and changed the dates of the example as well, which will turn it in a whole different thing, because now in one month you can have different rates.
timvw
Thank you, I'll try your code, but if I use dayofyear function what will happen when my dates overlaps from one year to the other ?
I think dayofyear function will only reach until 366 days
Saludos amigos ! (regards)
timvw
Thank you, I'll try your code, but if I use dayofyear function what will happen when my dates overlaps from one year to the other ?
I think dayofyear function will only reach until 366 days
Saludos amigos ! (regards)
You could also have a setup like rates(date, rate). And then for each date insert a rate...
Would return all the days and their rate..
And then would return a row (NULL, sum of rates)
Code: Select all
SELECT date, SUM(rate) AS total
FROM rates
WHERE date BETWEEN $start AND $end
GROUP BY date WITH ROLLUPAnd then would return a row (NULL, sum of rates)
Thank you timvw the last code that you provided works fine and do exactly what I needed.
But now I have another point:
I want to display the results of that query that you posted. but I want to display it in a table and add the word "total" at the end and display the grand total that the WITH ROLLUP funtion generates.
I want it to look like this:
01-01-2005 $10
02-01-2005 $30
TOTAL $40
I have not been able to find the way to add the word TOTAL right next to the gran total.
But now I have another point:
I want to display the results of that query that you posted. but I want to display it in a table and add the word "total" at the end and display the grand total that the WITH ROLLUP funtion generates.
I want it to look like this:
01-01-2005 $10
02-01-2005 $30
TOTAL $40
I have not been able to find the way to add the word TOTAL right next to the gran total.