separate distinct date

[neonpie]

hi all,

i'm using a mysql db. i have a table which lists all the dates for when a course starts. the field for the date is using the date type. i want to list of the courses in order of date, but under the monthly headings. how can i list distinct dates for the months.
i'm using date('F',strtotime($row['course_date'])) to get the name of the month from the date

sample dates:

2011-02-18
2011-02-25
2011-03-22

how i want it to be out put:

Feb
course title - 2011-02-18
course title - 2011-02-25

March
course title - 2011-03-22

[rocknbil]

Use date_format() [dev.mysql.com] . . . no need to jump hoops in programming.

Some PHP logic that should work:

$currenthead=$output=$headprinted=null;
$lightstyle="lightrow"; // set these in CSS
$darkstyle="darkrow";
$query = "select course_title,date_format(course_date,"%M") as mymonth, date_format(course_date,"%m/%d/%Y") as fulldate from table order by course_date desc";

$result = mysql_query($query) or die("cannot query classes: " . mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
 $month = $row['mymonth']; 
 $dt = $row['fulldate']; 
 $title = $row['course_title']; 
 $bg = ($bg==$lightstyle)?$darkstyle:$lightstyle; 
 // 
 // Print the head if it's not been printed OR if it changes. 
 // 
 if ((! $headprinted) or ($headprinted and ($month != $currenthead))) { 
 $output .= "<tr><th colspan=\"3\" style=\"background:#000;color:#fff;\">$month</th></tr>\n"; 
 $currenthead=$month; 
 $headprinted=1; 
 } 
 $output .= " 
 <tr> 
 <td class=\"$bg\">&nbsp;</td> 
 <td class=\"$bg\" nowrap>$title</td> 
 <td class=\"$bg\">$dt</td> 
 </tr> 
 "; 
} 
// 
if ($output) { echo "<table id=\"classes\">$output</table>"; } 
else { echo "<p>No classes available.</p>"; } 

Should give you a neatly formatted output with black-background month name headers under which are indented alternating colors for the rows, and days in the format MM/DD/YYYY. As said, this was typed on the fly and may contain syntax errors, but this should work.

[neonpie]

Hi rocknbil,

that worked a treat. the only thing i had to change was a slight change to the query as i have the list of courses in another table and just link the tables

you're a star!