inner while loop help for query results

[tomasz1]

Hello. I hope someone can help me out of a jam.

I'm have a data table with a date column containing a timestamp. I want to run a query that retrieves only distinct years that exist, and distinct months that exist.

For example. First query gets distinct years from the timestamp. I display them as links.

2006
2005
2004

When user clicks a year above, I want the year to drop open and display months that are available in that year.

My code below does this but, I want the other available years to continue to be displayed. This is what I get when user clicks 2004.

2004
  January
  March
  April 
  May
  June
  September
  October
  December.

This is fine, for 2004 has entries with those months in the timestamp, but I want to continue displaying the other available years as navigation links, as in:

2006
2005
2004
  January
  March
  April 
  May
  June
  September
  October
  December.

Obviously, there is some logic missing...well maybe obviously to someone other than myself ;-). Thanks for any help.

<?php
// query to get distinct year from videoDate timestamp column
$conn=odbc_connect('test','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql1="SELECT DISTINCT YEAR(videos.videoDate) as distinctYear FROM videos ORDER BY YEAR(videos.videoDate) DESC;";
$rs1=odbc_exec($conn,$sql1);
if (!$rs1)
  {exit("Error in SQL");}

echo '<ul>';
while (odbc_fetch_row($rs1))
{
// if this is there first time to the page, we display distinct years.
// if they have clicked the year, we use the year from the query string
$distinctYear = (isset($_GET['year'])) ? $_GET['year'] : odbc_result($rs1,"distinctYear");

echo "<li><a href='$_SERVER[PHP_SELF]?year=$distinctYear'>$distinctYear</a></li>";

// query to get distinct months.
if( isset($_GET['year']))
{
$selectedYear = $_GET['year'];
// query to get distinct months
// query to get distinct year from videoDate timestamp column
$sql2="SELECT DISTINCT MONTH(videos.videoDate) as distinctMonth FROM videos WHERE YEAR(videos.videoDate) = $selectedYear";
$rs2=odbc_exec($conn,$sql2);
if (!$rs2)
  {exit("Error in SQL");}

echo '<ul>';
while (odbc_fetch_row($rs2))
{
$theMonth=odbc_result($rs2,"distinctMonth");
switch($theMonth) {
case 1:
$formattedMonth = "January";
break;
case 2:
$formattedMonth = "February";
break;
case 3:
$formattedMonth = "March";
break;
case 4:
$formattedMonth = "April";
break;
case 5:
$formattedMonth = "May";
break;
case 6:
$formattedMonth = "June";
break;
case 7:
$formattedMonth = "July";
break;
case 8:
$formattedMonth = "August";
break;
case 9:
$formattedMonth = "September";
break;
case 10:
$formattedMonth = "October";
break;
case 11:
$formattedMonth = "November";
break;
case 12:
$formattedMonth = "December";
break;
}
echo "<li><a href='$_SERVER[PHP_SELF]?month=$theMonth&year=$selectedYear'>$formattedMonth</a></li>";
}
echo '</ul>';
break;

}
}
echo '</ul>';
odbc_close($conn);
    ?>

[tomasz1]

I solved this.

In case any else finds this useful, here's the fix:
The inner while loop condition is:
while (odbc_fetch_row($rs2) && $_GET['year'] == $distinctYear)



<?php
// query to get distinct year from videoDate timestamp column
$conn=odbc_connect('test','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql1="SELECT DISTINCT YEAR(videos.videoDate) as distinctYear FROM videos ORDER BY YEAR(videos.videoDate) DESC;";
$rs1=odbc_exec($conn,$sql1);
if (!$rs1)
  {exit("Error in SQL");}

echo '<ul>';
while (odbc_fetch_row($rs1))
{
// if this is there first time to the page, we display distinct years.
// if they have clicked the year, we use the year from the query string
// $distinctYear = (isset($_GET['year'])) ? $_GET['year'] : odbc_result($rs1,"distinctYear");
$distinctYear = odbc_result($rs1,"distinctYear");
echo "<li><a href='$_SERVER[PHP_SELF]?year=$distinctYear'>$distinctYear</a></li>";

// query to get distinct months.
if( isset($_GET['year']) && isset($_GET['year']) == $distinctYear)
{
$selectedYear = $_GET['year'];
// query to get distinct months
// query to get distinct year from videoDate timestamp column
$sql2="SELECT DISTINCT MONTH(videos.videoDate) as distinctMonth FROM videos WHERE YEAR(videos.videoDate) = $selectedYear";
$rs2=odbc_exec($conn,$sql2);
if (!$rs2)
  {exit("Error in SQL");}

echo '<ul>';
// ***HERE'S WHAT FIXED IT.
while (odbc_fetch_row($rs2) && $_GET['year'] == $distinctYear)
{
$theMonth=odbc_result($rs2,"distinctMonth");
switch($theMonth) {
case 1:
$formattedMonth = "January";
break;
case 2:
$formattedMonth = "February";
break;
case 3:
$formattedMonth = "March";
break;
case 4:
$formattedMonth = "April";
break;
case 5:
$formattedMonth = "May";
break;
case 6:
$formattedMonth = "June";
break;
case 7:
$formattedMonth = "July";
break;
case 8:
$formattedMonth = "August";
break;
case 9:
$formattedMonth = "September";
break;
case 10:
$formattedMonth = "October";
break;
case 11:
$formattedMonth = "November";
break;
case 12:
$formattedMonth = "December";

}
echo "<li><a href='$_SERVER[PHP_SELF]?month=$theMonth&year=$selectedYear'>$formattedMonth</a></li>";
}
echo '</ul>';


}
}
echo '</ul>';
odbc_close($conn);
    ?>