Sorting by Date and Month

lulubell

I have an events diary in a MySQl database, which I display the entries by date, starting with the latest date through to the oldest date; if the event date has passed the font changes to red.

All of this works, however I would like to show the results as

December
Event 1
Event 2

November
Event 3
Event 4

and so on.

The results I am geting is:

December
Event 1
December
Event2
November
Event3
November
Event4

This is my text:

$result = mysql_query ("SELECT diary_id, event_date, event, event_description, location, item_id, item_type, item_description from diary ORDER by event_date DESC, event ASC");
$numrows=mysql_num_rows($result);
echo "<TABLE CELLSPACING=\"0\" CELLPADDING=\"0\" BORDER=\"0\" WIDTH=\"740\">";
if ($numrows == 0)
{
echo "<TR><TD WIDTH=\"740\" HEIGHT=\"10\" COLSPAN=\"2\" CLASS=\"errText\">There are no results that match your query</TD></TR>\n";
}
if ($numrows > 0)
{
echo "<TR><TD WIDTH=\"740\" HEIGHT=\"10\" COLSPAN=\"2\" CLASS=\"mainText\">There are $numrows entires in the diary database. </TD></TR>\n";
echo "<TR><TD WIDTH=\"80\">Event Date</TD>";
echo "<TD WIDTH=\"225\">Event</TD></TR>\n";
while ($myrow = @mysql_fetch_array($result))
{
$diary_id=$myrow["diary_id"];
$event_date=$myrow["event_date"];
$event=stripslashes($myrow["event"]);
$event_description=stripslashes($myrow["event_description"]);
$location=stripslashes($myrow["location"]);
$item_id=$myrow["item_id"];
$item_type=stripslashes($myrow["item_type"]);
$item_description=stripslashes($myrow["item_description"]);
//Explode the date to show months
$e_date = explode("-", "$event_date");
$from_year = $e_date[0];
$from_month = $e_date[1];
$from_day = $e_date[2];
$new_event_date = $from_day . "-" . $from_month . "-" . $from_year;
$todays_date = gmdate("Y-M-d");

  if ($event_date < $todays_date)
    $font_class="CLASS=\"errText\"";
  else
    $font_class="CLASS=\"mainText\"";
  echo "<TR><TD WIDTH=\"740\" HEIGHT=\"5\" COLSPAN=\"5\" CLASS=\"boldText\">$event_month</TD></TR>";
  echo "<TR><TD WIDTH=\"80\" $font_class VALIGN=\"top\">$new_event_date</TD>";
  echo "<TD WIDTH=\"225\" $font_class VALIGN=\"top\">$event<BR>$event_description</TD></TR>";
  }
}
echo"</TABLE>";

bpat1434

The following line in the code needs some work:

echo "<TR><TD WIDTH=\"740\" HEIGHT=\"5\" COLSPAN=\"5\" CLASS=\"boldText\">$event_month</TD></TR>";

Perhaps it should be something more along the lines of:

if($cur_month != $old_month)
{
  echo "<TR><TD WIDTH=\"740\" HEIGHT=\"5\" COLSPAN=\"5\" CLASS=\"boldText\">$event_month</TD></TR>";
  // Let's update the $old_month var
  $old_month = $cur_month;
}

But where do $cur_month & $old_month come into play? Well, you'd have to add it to your loop. First, you'd have to declare the $old_month as an empty string outside your loop (as a default). Then in the loop make $cur_month have the value of $from_month. Now, do the if() check I gave above.

Now, with those modifications you get something like the following:

$result = mysql_query ("SELECT diary_id, event_date, event, event_description, location, item_id, item_type, item_description from diary ORDER by event_date DESC, event ASC");
$numrows=mysql_num_rows($result);
echo "<TABLE CELLSPACING=\"0\" CELLPADDING=\"0\" BORDER=\"0\" WIDTH=\"740\">";
if ($numrows == 0)
{
echo "<TR><TD WIDTH=\"740\" HEIGHT=\"10\" COLSPAN=\"2\" CLASS=\"errText\">There are no results that match your query</TD></TR>\n";
}
if ($numrows > 0)
{
echo "<TR><TD WIDTH=\"740\" HEIGHT=\"10\" COLSPAN=\"2\" CLASS=\"mainText\">There are <SPAN CLASS=\"boldText\">$numrows</SPAN> entires in the diary database.<BR><BR></TD></TR>\n";
echo "<TR><TD WIDTH=\"80\"><P CLASS=\"boldText\">Event Date</P></TD>";
echo "<TD WIDTH=\"225\"><P CLASS=\"boldText\">Event</P></TD></TR>\n";

$old_month = '';  // Default for $old_month.
while ($myrow = @mysql_fetch_array($result))
{
$diary_id=$myrow["diary_id"];
$event_date=$myrow["event_date"];
$event=stripslashes($myrow["event"]);
$event_description=stripslashes($myrow["event_description"]);
$location=stripslashes($myrow["location"]);
$item_id=$myrow["item_id"];
$item_type=stripslashes($myrow["item_type"]);
$item_description=stripslashes($myrow["item_description"]);
//Explode the date to show months
$e_date = explode("-", "$event_date");
$from_year = $e_date[0];
$from_month = $cur_month = $e_date[1]; // Added the $cur_month attribute.
$from_day = $e_date[2];
$new_event_date = $from_day . "-" . $from_month . "-" . $from_year;
$todays_date = gmdate("Y-M-d");

if ($event_date < $todays_date)
$font_class="CLASS=\"errText\"";
else
$font_class="CLASS=\"mainText\"";
echo "<TR>";

if($cur_month != $old_month) // i.e. the month has changed
{
  echo "<TD WIDTH=\"740\" HEIGHT=\"5\" COLSPAN=\"5\" CLASS=\"boldText\">$event_month</TD></TR>";
  $old_month = $cur_month;
}

echo "<TR><TD WIDTH=\"80\" $font_class VALIGN=\"top\">$new_event_date</TD>";
echo "<TD WIDTH=\"225\" $font_class VALIGN=\"top\">$event<BR>$event_description</TD></TR>";
}
}
echo"</TABLE>";

lulubell

Thank you that worked perfectly, I don't suppose you know how I can write the month as January, February, etc instead of 01, 02?

I was using a switch statement I.e.

switch($from_month)
{
case "01":
$event_month = "JANUARY";
break;
case "02":
$event_month = "FEBRUARY";
break;
default:
}

But this is so long winded, I think I should be writing an array, but not sure quite how?

Any help would be much appreciated.

bpat1434

Hmm... you could do a switch, you could use a numericaly indexed array, or you could just use the date() function:

date('M', $from_month);