homepage Welcome to WebmasterWorld Guest from 54.166.108.167
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
neonpie




msg:4268943
 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:

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




msg:4269132
 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:

$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




msg:4269150
 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