Jump to content

inner while loop help for query results


tomasz1

Recommended Posts

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);
    ?>
Link to comment
Share on other sites

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);
    ?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.