Automated List Display based on Academic Year

[sw9]

Hi All,

 

I need to display a list of videos by academic year. Each academic year runs from September to August of the following year. Here is how I need to organize it:

 

1. First get the earliest year based on a Y-m-d field in mySQL.

 

Then break out each academic year in a list, organizing it like so:

 

Current Year (2007-2008)

Video 1

Video 2

Video 3

 

Last Year (2006-2007)

Video 1

Video 2

Video 3

 

Academic Year 2005-2006

 

And so on until it gets back to the earliest year. The part that is getting me is the fact that it's an academic year, so it's not a clean break. I am also struggling with arrays and I know pulling this data from the database is going to involve that. Can anyone help me through this? I am assuming I need to do the date processing with PHP once I pull all of the data into an array, so my mySQL statement will not have any parameters. In my code so far I am pretty clueless as to how to approach this, but below is what I have so far:

 

<table width="85%" align="left" border="0" cellspacing="0" cellpadding="4">
  <tr><td colspan=2 class="textbold"> </td></tr>

  <tr class="textbold">
  
<td nowrap bgcolor="#777257"><span style="color:#FFFFFF">Episode</span></td>
    <td nowrap bgcolor="#777257"><span style="color:#FFFFFF">Production Date</span></td>

  </tr>
<?php

//Here we can pull the current year and then set the academic months
$thisYear = date('Y');
$lastYear = date('Y') - 1;
$monthStart = '0901';
$monthEnd = '0801';

//Here I'm trying to set it for the first year, but I know this isn't the best way to do it as it would get REALLY redundant, and I don't know what the earliest year is.
$yearEnd = $thisYear.$monthEnd;
$yearStart = $lastYear.$monthStart;

$sql = "SELECT a.episode_name, a.acq_date FROM mprogram_library 
		ORDER BY a.acq_date DESC";

$rval = mysql_query($sql);		
$row = 0;
while( $data = mysql_fetch_assoc( $rval ) ){ 
	++$row;
	$bgColor = $row % 2 == 0?"#CBC497":"#E4DFC6";
	$program_link = "CRV-VOD.php?title=".$data["title"];



?> 
<tr style="background:<?php echo $bgColor; ?>;">
    <td><a href="<?php echo $program_link; ?>" target="_blank" class="schedulelinks"><?php if( $data["episode_name"] && $data["episode_name"] != "" ){ echo $data["episode_name"]; }else{ echo " "; } ?></a></td>
    <td><a href="<?php echo $program_link; ?>" target="_blank" class="schedulelinks"><?php if( $data["acq_date"] && $data["acq_date"] != "" ){ echo retn_date($data["acq_date"]); }else{ echo " "; } ?></a></td>

</tr></table>

and then all the filtering needs to take place in the php processing?

 

Thanks so much for help in advance....

sw

[amites]

what format is your timestamp in?

 

one method would be to pull your data with the timestamp as a Unixtimestamp give a single number to deal with for comparing month-day-year as opposed to several sections

 

there are more efficient ways to do this but that seems like one of the easier,

 

take a look at http://us3.php.net/manual/en/function.date.php

 

and take a gander of mktime while your at it

[sw9]

Hi,

 

Thanks for your suggestion. My date is not a timestamp; it's just a date field with year month day....