how do I echo year headers on schedule?

[acuken]

I've got a class schedule that spans two years and I would like to have the years as headers. startDate is unix date field.

 

Here is existing code:

 

$sql = "SELECT *

        FROM classOfferings, classes, instructors

WHERE classes.classId = classOfferings.classId

AND classOfferings.instructorId = instructors.instructorId

AND classOfferings.startDate >= CURDATE()

 

ORDER BY classOfferings.startDate";

 

 

if(! $retval )

{

  die('Could not get schedule: ' . mysql_error());

}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))

{

echo "{$row['dates']}";

if ($row['hours'] != 0){

        echo "</td> ";

}

else {

echo "CANCELLED</td>";

}

echo "<td class='cell2'>".

"<a href='classDetail.php?seq={$row['seq']}'>{$row['title']}</a>".

"{$row['days']}".

        "{$row['times']}".

        "<a href='instructorDetail.php?instructorId={$row['instructorId']}'>{$row['fName']} "." ".

"{$row['lName']}</a></td>}

    echo "</td></tr></table></div>";

}

[Psycho]

I'm having a hard time following the logic in your code with respect to how the output is created. For example you start the output with closing </td> tags based on a value from the current record. That means you are closing the previous record with different values based on the next record? Doesn't make sense to me. And each record is closing a tale, but not opening one ? ? ?

[acuken]

whoops. Just trying to make it read easier and erased beginning of table.

So each record is it's own table because that's how I could make it go.

 

Corrected code:

 

$sql = "SELECT *

        FROM classOfferings, classes, instructors 

  WHERE classes.classId = classOfferings.classId

  AND classOfferings.instructorId = instructors.instructorId

  AND classOfferings.startDate >= CURDATE()

 

  ORDER BY classOfferings.startDate";

 

 

if(! $retval )

{

  die('Could not get schedule: ' . mysql_error());

}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))

{

  echo "<table id='schedule'><tr><td>{$row['dates']}";

  if ($row['hours'] != 0){

          echo "</td> ";

      }

  else {

      echo "CANCELLED</td>";

      }

  echo "<td class='cell2'>".

      "<a href='classDetail.php?seq={$row['seq']}'>{$row['title']}</a>".

      "{$row['days']}".

            "{$row['times']}".

            "<a href='instructorDetail.php?instructorId={$row['instructorId']}'>{$row['fName']} "." ".

    "{$row['lName']}</a></td>}

    echo "</td></tr></table></div>";

}

[BigTime]

Id do it in a foreach loop

 

    
# setup SQL statement
    $SQL = " SELECT DISTINCT year from table ORDER by year ASC ";
    # execute SQL statement
    $retyear = mysql_db_query($db, $SQL, $conn);
# check for errors
    if (!$retyears) { echo( mysql_error()); }

else {
	 while ($data=mysql_fetch_array($retyears)){
	 $years=$data[year];

	 $yeararray = array($year);

	 foreach ($yeararray as $key => $y)

		{

# setup SQL statement
    $SQL = " SELECT all my call stuff ";

# execute SQL statement
    $getclassstuff = mysql_db_query($db, $SQL, $conn);

    # check for errors
    if (!$getclassstuff ) { echo( mysql_error()); }
else {

# check for records
$total = mysql_num_rows($getclassstuff );
if ($total >= 1) {
    echo ("<font face=arial size=3 color=#e0e0e0><b>$y</b><BR>");
echo ("<TABLE cellpadding=2 border=1 width=99% style=\"border-collapse: collapse; border: solid; border:1px;\"><tr><td>TABLE COLUMN HEADER</td><td>TABLE COLUMN HEADER</td><td>TABLE COLUMN HEADER</td><td>TABLE COLUMN HEADER</td></tr>"); etc....

	while ($row = mysql_fetch_array($getclassstuff )) {
	$year = $row["year"];
	DECLARE MY OTHER VALUES HERE



echo ("td>$TABLE COLUMN DATA ROW</td><td>$TABLE COLUMN DATA ROW</td><td>$TABLE COLUMN DATA ROW</td><td>$TABLE COLUMN DATA ROW</td></tr>");
}?></Table>

[Psycho]

Here's an attempt at what I think you need. Adding the part to display the year is not a problem, it is fixing the rest of the code that is creating invalid HTML that I guessed at. I changed it to create ONE table to display all the records. Creating separate tables seems odd since the columns will not line up properly. I also revised the query to use JOINs.

 

<?php

$sql = "SELECT dates, seq, title, days, times, instructorId, fName, lName, startDate
        FROM classOfferings
        JOIN classes ON classOfferings.classId = classes.classId
        JOIN instructors ON classOfferings.instructorId = instructors.instructorId
        WHERE classOfferings.startDate >= CURDATE()
        ORDER BY classOfferings.startDate";
$result = mysql_query($sql);

if(!$result)
{
  die('Could not get schedule: ' . mysql_error());
}

if(mysql_num_rows($result)==0)
{
    echo "There was no data available.";
}
else
{
    //There was data, display the results
    $currentYear = ''; //Var to track changes in year

    //Open table
    echo "<div><table>\n";
    while($row = mysql_fetch_assoc($result))
    {
        //Determine needed variables
        $status = ($row['hours'] != 0) ? '' : 'CANCELLED';
        $year = date('Y', strtotime($row['startDate']));

        //Show year header if changed
        if($currentYear != $year)
        {
            $currentYear = $year;
            echo "<tr><th colspan='3'>{$currentYear}</th></tr>\n";
        }

        //Display the record
        echo "<tr>\n";
        echo   "<td>{$row['dates']}</td>\n";
        echo   "<td>{$status}</td>\n";
        echo   "<td class='cell2'>";
        echo     "<a href='classDetail.php?seq={$row['seq']}'>{$row['title']}</a>";
        echo     "{$row['days']}{$row['times']}";
        echo     "<a href='instructorDetail.php?instructorId={$row['instructorId']}'>{$row['fName']}  {$row['lName']}</a>";
        echo   "</td>\n";
        echo "<tr>\n";
    }

    //Close table
    echo "</div></table>\n";
}

?>

[Psycho]

@BigTime: You should never run queries in loops. It does not scale and your site would crash/hang as you added more data/users. Lear to use joins and process the data correctly. acuken had the right idea with one qury to get all th edata. Then you just use a variable (such as $currentYear above) to identify when there is a change in the data. You just need to ensure you sort the data appropriately beforehand.

[acuken]

Thanks for everything, much respect to you.