tomasz1 Posted October 2, 2006 Share Posted October 2, 2006 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.200620052004When 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:200620052004 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); ?> Quote Link to comment Share on other sites More sharing options...
tomasz1 Posted October 2, 2006 Author Share Posted October 2, 2006 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); ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.