Welcome to WebmasterWorld Guest from 23.20.223.88

Forum Moderators: open

Message Too Old, No Replies

separate distinct date

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

5+ Year Member



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
6:15 pm on Feb 18, 2011 (gmt 0)

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



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.
6:44 pm on Feb 18, 2011 (gmt 0)

5+ Year Member



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!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month