From Date and To date - Insert data between 2 point of dates

[mohsini]

Hello All

I have a small task but im very new to PHP so i dont know how to do. The task is, i have a HTML form and it has two text fields. 'FromDate " and "ToDate". The user can select any date from the calendar in these two dates and can submit.

Once a user submits, the php code will update the column in Database from the fromdate and todate which he has selected. How can I do this. the problem is lets say if the user selects Fromdate as 15/01/2008 and todate as 13/11/2008, how can i insert the records, between these two days taking care of 30days month and 31 day months

Plz reply as soon as possible.

Regards

[bretticus]

How can I do this. the problem is lets say if the user selects Fromdate as 15/01/2008 and todate as 13/11/2008, how can i insert the records, between these two days taking care of 30days month and 31 day months

Are you saying that you plan to submit (insert) a record for each calendar date (days) between the "from" date and the "to" date?

[mohsini]

Hi brett

yes i need to insert single record for each calander days. i dont know how to code in php for inserting rows for each days. plz advice and help how to do

[bretticus]

Okay, I won't question your reasons (too much) , but it seems like making a record for each day per user is a bit of overhead. Are you storing information for every single day per user???

Okay, now that I'm over that...

Unix time is your friend. Unix time is counted in seconds transpired from Jan 1st, 1970. At the time of writing, 1214701234 seconds have passed according to my computer. I got this result via the PHP time() function. And more importantly, the timestamp parameter of the PHP function date() takes a Unix timestamp.

That being said you can convert your times (and do validation also because an improper date string returns false.) using the PHP function strtotime().

Okay, now we now that there are 24 hours in a day. 60 minutes in an hour. And 60 seconds in a minute. So one day's worth of Unix time is 24x60x60 or 86400 seconds per day.

Now we have a simple loop on our hands.

<?php
$start = strtotime($_POST['start']);
$stop = strtotime($_POST['stop']);
for ($seconds=$start; $seconds<=$stop; $seconds+=86400)
{
	//convert to mysql date
	$date = date("Y-m-d", $seconds);
}
?>

In other words, the variable $seconds gets a day's worth of seconds (86400) per iteration of the loop (it advances by one day per iteration.) To get the current date, just use the date function.

[mohsini]

HI Brett

Sorry mate you , u got all wrong. I want to insert records at once. I have fromdate and todate, and once the user submits the form, i will insert row for all the days between fromdate and todate.

I dont want to do everyday. I wanan do at the same time when the user subits the form. Let me explain u clearly. I have a datefield in my form which says' Please select your start date" and I have another datefield which says enter end date. And then he submits the form. So once the form is submited, the php file should insert rows fromthe start date till the end date.

I hope im clear now

Regards

[bretticus]

I have a datefield in my form which says' Please select your start date" and I have another datefield which says enter end date. And then he submits the form. So once the form is submited, the php file should insert rows fromthe start date till the end date.

Nope, I believe I did exactly as you described.

php file should insert rows fromthe start date till the end date.

Did you even read my post or try the sample code?

[mohsini]

Hey Hii brett,
thanks a million for your quick reply, but sorry , pzl dont get offended, i still feel, thats what what my question is let me tell you the whole scenario. The application which Im making is about car rental system. The rent a car manager, should be able to insert car rate for the day. and as you know the rates can be seasonal and can vary from months to monts. so lets say from the mid of january till the end of april the business is dry and he wants to set the daily rental fare to US$200. so he will logg in and he will get this form which will say pla enter start day (he will put 15/01/208) and till mid of april (15/04/2008). and he will mention the rate($S200), so NOWW, once he posts the form, my form will have the action as "insert_rates.php", this php file will get start date, enddate and rate. It should then insert in my database the following record

companyID CarID date rate
1 5 15/01/2008 200

1 5 16/01/2008 200
1 5 17/01/2008 200
.
.
.
.
and so on till the last day.......

HOw do i do this., this is my task......

[mohsini]

Hello Brett,

i did the same thing which u mentioned, but ti doesnt work

<?php
$start = strtotime("30/06/2008");
$stop = strtotime("15/12/2008");
for ($seconds=$start; $seconds<=$stop; $seconds+=86400)
{
//convert to mysql date
$date = date("Y-m-d", $seconds);
echo($date."<br>");
}
?>

[NogDog]

You can use the [man]strtotime/man function with the "+1 day" parameter to cycle through all the possible dates.

However, this will result in either (a) a separate insert query for each date in the range, or (b) one really large query with a values clause for each date. The first if very inefficient and the second risks creating a query which is too long.

If I were doing it, I'd have a rates table where each row includes a start date and end date, plus a timestamp field to record when it was instituted:

id  (int, primary key, auto-increment)
company_id (int?)
car_id (int?)
start_date (date)
end_date (date)
time_entered (timestamp)

Then when you need to know the rate for a given date:

$query = "
SELECT * FROM rates 
  WHERE 
    company_id = $compId AND 
    car_id = $carId AND
    $date BETWEEN start_date AND end_date
  ORDER BY time_entered DESC
  LIMIT 1
";

This would then give you the most recently assigned rate for that date.

[mohsini]

Hi NOGDOG

Thnks for yourreply, im really a newbie in PHP , can you please explicitly give me the sample with the correct parameters. Here are my parameters, lets say if the user wants to insert the rates from 01/10/2008 till 28/12/2008, then what will be the php code, acn u plz give me for the above mentioned dates.

Thanks a millions,

[NogDog]

mohsini wrote:

Hi NOGDOG

Thnks for yourreply, im really a newbie in PHP , can you please explicitly give me the sample with the correct parameters. Here are my parameters, lets say if the user wants to insert the rates from 01/10/2008 till 28/12/2008, then what will be the php code, acn u plz give me for the above mentioned dates.

Thanks a millions,

For which method, yours or mine?

[mohsini]

Hi I got your suggestion NogDog, but i have a quetion, if i design my DB as per your suggestion then what wud happen if the user inserts the rates for overlapping dates. example

15/02/2008 - 29/04/2008 rate = 200

01/03/2008 - 15/04/2008 rate = 300.

SO in this case what will be the rate?What will the query give me if i want to know the rate for the date 12/04/2008???

i just wanna understand ur logic dude thats it.

[mohsini]

Right now give me for my method, but im still trying to understand your logic....which you just suggeted. sinceu r a guru, that has to be better than mine

[NogDog]

mohsini wrote:

Hi I got your suggestion NogDog, but i have a quetion, if i design my DB as per your suggestion then what wud happen if the user inserts the rates for overlapping dates. example

15/02/2008 - 29/04/2008 rate = 200

01/03/2008 - 15/04/2008 rate = 300.

SO in this case what will be the rate?What will the query give me if i want to know the rate for the date 12/04/2008???

i just wanna understand ur logic dude thats it.

The most recently entered value for that date would be used (as a result of the descending sort on the timestamp field).

[NogDog]

Creating an insert with multiple values:

<?php
// sample data from user input:
$start_date = ('2008-06-01');
$end_date = ('2008-06-30');
$rate = 99;
$company_id = 11;
$car_id = 22;

// create values for each date:
$startTime = strtotime($start_date);
$endTime = strtotime($end_date);
$values = array();
for($time = $startTime; $time <= $endTime; $time = strtotime('+1 day', $time))
{
   $thisDate = date('Y-m-d', $time);
   $values[] = "($company_id, $car_id, $rate, '$thisDate')";
}

// build the actual query:
$query = sprintf(
   "INSERT INTO rates (companyID, carID, rate, date) VALUES\n%s",
   implode(",\n", $values)
);

// show what query would look like:
echo "<pre>$query</pre>";

Sample output:

INSERT INTO rates (companyID, carID, rate, date) VALUES
(11, 22, 99, '2008-06-01'),
(11, 22, 99, '2008-06-02'),
(11, 22, 99, '2008-06-03'),
(11, 22, 99, '2008-06-04'),
(11, 22, 99, '2008-06-05'),
(11, 22, 99, '2008-06-06'),
(11, 22, 99, '2008-06-07'),
(11, 22, 99, '2008-06-08'),
(11, 22, 99, '2008-06-09'),
(11, 22, 99, '2008-06-10'),
(11, 22, 99, '2008-06-11'),
(11, 22, 99, '2008-06-12'),
(11, 22, 99, '2008-06-13'),
(11, 22, 99, '2008-06-14'),
(11, 22, 99, '2008-06-15'),
(11, 22, 99, '2008-06-16'),
(11, 22, 99, '2008-06-17'),
(11, 22, 99, '2008-06-18'),
(11, 22, 99, '2008-06-19'),
(11, 22, 99, '2008-06-20'),
(11, 22, 99, '2008-06-21'),
(11, 22, 99, '2008-06-22'),
(11, 22, 99, '2008-06-23'),
(11, 22, 99, '2008-06-24'),
(11, 22, 99, '2008-06-25'),
(11, 22, 99, '2008-06-26'),
(11, 22, 99, '2008-06-27'),
(11, 22, 99, '2008-06-28'),
(11, 22, 99, '2008-06-29'),
(11, 22, 99, '2008-06-30')

[mohsini]

OK Buddy!!! lovely, yes thats the objective what i want. Ok now thats one end of this. You now know what i want. So can you now advice me is there a better way to do rather than populating the tables with so many rows.

The method which you suggested, can it do exactly the same thing. Basically, will the rent a car company change and modify the rates whenever they want and insert new rates with new dates or they can just insert, wht if they want to change the rates for the dates which they had inserted............sorry if i sould stupid, but i just want to understand your logic. Im very new to php

Thanks a lot really appreciate it mate

[mohsini]

NogDog

If i go about your method, then i have a problem of select query, how to display. Now if i need to show and display the rates for the dates which user wants to see how can I. Remember its not a single date. its a RANGE of dates.

Plz tell me what if a user wants the see the rates for 01/07/2008 - 15/08/2005, in this case how can i get the rates for ALL the dats between this fromdate and todate.

Regards