How To Create A Dropdown listing between two Date Ranges

[KevMull]

I have a mySQL table with one record showing a startDate and endDate.

 

I need to create a dropdown list showing just the month and year only of all the dates that fall within the date range including the start and end date.

 

Many Thanks

[.josh]

date()

 

good luck.

 

[Adam]

Hah I went through this just the other day, was a bit of a nightmare.. I'll give you a big help though and show you the MySQL syntax for selecting between two dates:

 

SELECT * FROM yourTable WHERE yourDateField BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'

 

That assumes in the database table each record has a single date. Then the range of dates will replace the two YYYY-MM-DD 's, i don't know if you have two fixed dates in mind or if you're going to have a form for the user to select.. but that should help you on your way..

[KevMull]

The mySQl table has one row and two date fields (Start and End), there are no other dates or rows.

 

The problem i'm having is the code in the PHP to create the dropdown of month and year (as one string) as a list from the above Start and and end range.

 

 

 

[PFMaBiSmAd]

An actual example of what you have in the database and what you want to achieve would be worth a thousand words.

[.josh]

I have a mySQL table with one record showing a startDate and endDate.

 

I need to create a dropdown list showing just the month and year only of all the dates that fall within the date range including the start and end date.

 

Many Thanks

date()

 

good luck.

 

 

sorry I totally missed the first part where you mentioned the mysql table. 

[JasonLewis]

-- Err.. others posted. Sorry --

[PFMaBiSmAd]

So, if we assume that your dates in your database are proper DATE or DATETIME data types (and if they are not, changing them would be your first step in solving this problem), give the following code a try (tested) -

 

<?php
// Given a startDate and endDate, do a dropdown with yyyymm values including the start and end
// Assume end is same or after start (code does not check)
// Sql query to get the data: SELECT EXTRACT(YEAR_MONTH FROM startDate) as start, EXTRACT(YEAR_MONTH FROM endDate) as end FROM your_table WHERE your_where_conditions
// This will give a fetched result as $row['start'] and $row['end'] values yyyymm - put into $start and $end variables for program to use
$start = "200801"; // test value
$end = "200901"; // test value

// function to increment $start and return next yyyymm or a false when next yyyymm is greater than $end
function next_yyyymm($start,$end) {
$st = str_split($start,4); // [0] is yyyy [1] is mm
$start = date("Ym",strtotime("{$st[0]}-{$st[1]}-01 + 1 month"));
if($start > $end) {
	return false;
}
return $start;
} // end of function

echo "Start: $start, end: $end<br />"; // show values for testing

// list will always have the initial starting yyyymm as a choice (found an instance where a do-while loop can be used)
$options = "";
do {
$options .= "<option value=\"$start\">$start</option>\n"; // format the displayed output as necessary
} while($start = next_yyyymm($start,$end));
?>
<form method="post" action = "yourformaction.php">
<select name="yyyymm">
<?php echo $options; ?>
</select>
<input type="submit">
</form>

 

[KevMull]

Thank you FMaBiSmAd

 

That's seems to have done teh trick.

 

Much appreciated.