Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

separate distinct date

10:09 am on Feb 18, 2011 (gmt 0)

New User

10+ Year Member

joined:Feb 23, 2006
posts: 39
votes: 0

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:


how i want it to be out put:

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

course title - 2011-03-22
6:15 pm on Feb 18, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

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

Some PHP logic that should work:

$lightstyle="lightrow"; // set these in CSS
$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";
$output .= "
<td class=\"$bg\">&nbsp;</td>
<td class=\"$bg\" nowrap>$title</td>
<td class=\"$bg\">$dt</td>
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.
6:44 pm on Feb 18, 2011 (gmt 0)

New User

10+ Year Member

joined:Feb 23, 2006
posts: 39
votes: 0

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!