Got a problem with the insertion of the date, I am trying to enter a date range into my mysql table. There are two dates coming from a javascript calendar. Here is the php code, the problem here is after getting the from date, it cannot go beyond the from month, if the from and to month are same, $days which is the difference between the two dates works in the same manner as in the case of different from and to months, i.e. if the from date is 23.07.2008 and the to date is 28.07.2008, $days returns 6 and if the to date is changed to 28.09.2008, then too $days returns 6. If I change $days manually to say 10 keeping from date to 23.07.2008, mysql inserts 0000-00-00 after inserting 31.07.2008 in the date column .

include "../connection.php";

//Getting the From Date
$from = $_POST['start'];
$dateTime = new DateTime($from); 
$sqlfrom=date_format ( $dateTime, 'Y-m-d' ); 

//Getting the To Date
$to = $_POST['stop'];
$dateTime1 = new DateTime($to); 
$sqlto=date_format ( $dateTime1, 'Y-m-d' ); 

$rate = $_POST['rate'];
//Getting the no. of days to insert
$days = $to - $from + 1;

//echo $days . "<br>";

for ($i=1; $i<=$days; $i++)
{
//echo $sqlfrom ." <br>";
$query = "INSERT INTO test (date, rate) ". "VALUES ('$sqlfrom', '$rate')"; 

mysql_query($query) or die('Error, query failed : ' . mysql_error()); 
$sqlfrom++;

}

    Got from a post from NogDog. The code should be

    include "../connection.php";

//Getting the From Date
$from = $_POST['start'];


//Getting the To Date
$to = $_POST['stop'];


$rate = $_POST['rate'];


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

// build the actual query:
$query = sprintf(
   "INSERT INTO test (date, rate) VALUES\n%s",
   implode(",\n", $values)
);
mysql_query($query) or die('Error, query failed : ' . mysql_error()); 


?>
      Write a Reply...