Having trouble comparing a range of dates entered in a form with records in a mySQL database
- by Andrew Fox
I have a table called schedule and a column called Date where the column type is date. In that column I have a range of dates, which is currently from 2012-11-01 to 2012-11-30. I have a small form where the user can enter a range of dates (input names from and to) and I want to be able to compare the range of dates with the dates currently in the database.
This is what I have:
////////////////////////////////////////////////////////
//////First set the date range that we want to use//////
////////////////////////////////////////////////////////
if(isset($_POST['from']) && ($_POST['from'] != NULL))
{
$startDate = $_POST['from'];
}
else
{
//Default date is Today
$startDate = date("Y-m-d");
}
if(isset($_POST['to']) && ($_POST['to'] != NULL))
{
$endDate = $_POST['to'];
}
else
{
//Default day is one month from today
$endDate = date("Y-m-d", strtotime("+1 month"));
}
//////////////////////////////////////////////////////////////////////////////////////
//////Next calculate the total amount of days selected above to use as a limiter//////
//////////////////////////////////////////////////////////////////////////////////////
$dayStart = strtotime($startDate);
$dayEnd = strtotime($endDate);
$total_days = abs($dayEnd - $dayStart) / 86400 +1;
echo "Start Date: " . $startDate . "<br>End Date: " . $endDate . "<br>";
echo "Day Start: " . $dayStart . "<br>Day End: " . $dayEnd . "<br>";
echo "Total Days: " . $total_days . "<br>";
////////////////////////////////////////////////////////////////////////////////////
//////Then we're going to see if the dates selected are in the schedule table//////
////////////////////////////////////////////////////////////////////////////////////
//Select all of the dates currently in the schedule table between the range selected.
$sql = ("SELECT Date FROM schedule WHERE Date BETWEEN '$startDate' AND '$endDate' LIMIT $total_days");
//Run a check on the query to make sure it worked. If it failed then print the error.
if(!$result_date_query = $mysqli->query($sql))
{
die('There was an error getting the dates from the schedule table [' . $mysqli->error . ']');
}
//Set the dates to an array for future use.
// $current_dates = $result_date_query->fetch_assoc();
//Loop through the results while a result is being returned.
while($row = $result_date_query->fetch_assoc())
{
echo "Row: " . $row['Date'] . "<br>";
echo "Start day: " . date('Y-m-d', $dayStart) . "<br>";
//Set this loop to add 1 day to the Start Date until it reaches the End Date
for($i = $dayStart; $i <= $dayEnd; $i = strtotime('+1 day', $i))
{
$date = date('Y-m-d',$i);
echo "Loop Start day: " . date('Y-m-d', $dayStart) . "<br>";
//Run a check to see if any of the dates selected are in the schedule table.
if($row['Date'] != $date)
{
echo "Current Date: " . $row['Date'] . "<br>";
echo "Date: " . $date . "<br>";
echo "It appears as though you've selected some dates that are not in the schedule database.<br>Please correct the issue and try again.";
return;
}
}
}
//Free the result so something else can use it.
$result_date_query->free();
As you can see I've added in some echo statements so I can see what is being produced. From what I can see it looks like my $row['Date'] is not incrementing and staying at the same date. I originally had it set to a variable (currently commented out) but I thought that could be causing problems.
I have created the table with dates ranging from 2012-11-01 to 2012-11-15 for testing and entered all of this php code onto phpfiddle.org but I can't get the username provided to connect.
Here is the link: PHP Fiddle
I'll be reading through the documentation to try and figure out the user connection problem in the meantime, I would really appreciate any direction or advice you can give me.
