homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

separate distinct date

5+ Year Member

Msg#: 4268941 posted 10:09 am on Feb 18, 2011 (gmt 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



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

Msg#: 4268941 posted 6:15 pm on Feb 18, 2011 (gmt 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.


5+ Year Member

Msg#: 4268941 posted 6:44 pm on Feb 18, 2011 (gmt 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!

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved