Dividing results with 'group' headings
Moderator: General Moderators
Dividing results with 'group' headings
I don't think I'm using the correct terminology, as I keep coming back to GROUP BY used for summarising, etc., so if someone could point me at the right bit of the manual to do what I'm trying to do it would be appreciated! One day I'll have learnt to speak mySQL properly!
Basically, I want to have a news table, with date and headline fields (and a couple of other bits for filtering) and want to be able to list the results with a 'group' heading by month.
It's easy enough to give all of the results, even filtered by date as a single list, e.g.
01/01/06 - News story 1
02/01/06 - News story 2
05/05/06 - News story 3
But what I'd like to do is give:
January
01/01/06 - News story 1
02/01/06 - News story 2
May
05/05/06 - News story 3
Hope that makes sense. Thanks to anyone who can point me in the direction!
Basically, I want to have a news table, with date and headline fields (and a couple of other bits for filtering) and want to be able to list the results with a 'group' heading by month.
It's easy enough to give all of the results, even filtered by date as a single list, e.g.
01/01/06 - News story 1
02/01/06 - News story 2
05/05/06 - News story 3
But what I'd like to do is give:
January
01/01/06 - News story 1
02/01/06 - News story 2
May
05/05/06 - News story 3
Hope that makes sense. Thanks to anyone who can point me in the direction!
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
How are the dates stored? If they are native DATE (et al) types, you can use DATE_FORMAT() or EXTRACT() to make the month pop out for easy reading. If it's not a native date, you'll have to parse it in some fashion and do the same end run.
If you want to know more, I talked about this sort of stuff at length in a thread that's linked from Useful Posts (follow the Forum Tour link in my signature.)
If you want to know more, I talked about this sort of stuff at length in a thread that's linked from Useful Posts (follow the Forum Tour link in my signature.)
Thanks for the speedy response. The dates are all stored as date types.
I've had a quick trawl through your postings including DATE_FORMAT() and EXTRACT() but I think I haven't phrased my original query properly. The date formatting and extracting of parts of the date (e.g. month) are all valuable and make good sense (hopefully I would have been okay with those bits).
The bit I don't know where to start looking in the manual for is how to create the heading lines, so that all of the results are listed but with summary headings (talking from a WYSIWIG point of view and MS Access reports, using the wizards or manually, where you can group by various headings and sub-headings - what I'm trying to do is work out how to show the headings in my 'report' or output), i.e.
so my output would be along the lines of that shown in my initial post, with all of the news headlines put out but broken up under month headings. I'm okay with the formatting, but don't know where to start looking for this in the manual. I'd be okay with the theory if it was working with a one-to-many relationship and showing the entry in one table then the related entries in another, but as this is all from one table I don't know.
Thanks again for any help or pointers-in-the-right-direction you can offer.
Adam
I've had a quick trawl through your postings including DATE_FORMAT() and EXTRACT() but I think I haven't phrased my original query properly. The date formatting and extracting of parts of the date (e.g. month) are all valuable and make good sense (hopefully I would have been okay with those bits).
The bit I don't know where to start looking in the manual for is how to create the heading lines, so that all of the results are listed but with summary headings (talking from a WYSIWIG point of view and MS Access reports, using the wizards or manually, where you can group by various headings and sub-headings - what I'm trying to do is work out how to show the headings in my 'report' or output), i.e.
Code: Select all
{group heading(1)}
{result 1}
{result 2}
{group heading(2)}
{result 3}Thanks again for any help or pointers-in-the-right-direction you can offer.
Adam
Thanks again. I think I'm starting to vaguely understand. Do you mean I'll end up with output along the lines of (in terms of words not code) the following?
IF month-in-record-(i) <> month-in-record-(i-1)
{output current-record-month (formatted as heading)}
output current record as a standard result
So many useful posts, it's not easy starting off but hopefully I'll get there!
IF month-in-record-(i) <> month-in-record-(i-1)
{output current-record-month (formatted as heading)}
output current record as a standard result
So many useful posts, it's not easy starting off but hopefully I'll get there!
I may be about to make a complete fool of myself, but anyway ... is the following heading in the right direction?
Code: Select all
mysql_connect($server,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
SELECT (date,Headline,URL,Text FROM news ORDER BY date ASC)
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
if ($num==0)
{
echo "No current news stories to display";
} else {
$i=0;
while ($i < $num) {
$newsHeadline=mysql_result($result,$i,"newsHeadline");
$newsURL=mysql_result($result,$i,"newsURL");
$newsText=mysql_result($result,$i,"newsText");
$newsMonth=date_format(extract(month FROM $result,$i,"date"),%M)
if (extract(month FROM $result,$i,"date") <> extract(month FROM $result,$i-1,"date")) {
echo "<p class='newsmonth'>$newsMonth</p>";
}
echo "<p class='news'><a class='newshead' href='$newsURL'>$newsHeadline</a><br />$newsText</p>";
$i++;
}Making progress, but I've got stuck on the key point.
Any pointers on how (or where to find out how) I compare a value in the current record with a value in the previous record?
i.e if ('Month'in[CurrentRecord] != 'Month'in[PreviousRecord] )
Basically, how do I refer to the previous record? (I tried "$i-1" but that was obviously too straightforward!
Thanks again for any help
Adam
Any pointers on how (or where to find out how) I compare a value in the current record with a value in the previous record?
i.e if ('Month'in[CurrentRecord] != 'Month'in[PreviousRecord] )
Basically, how do I refer to the previous record? (I tried "$i-1" but that was obviously too straightforward!
Thanks again for any help
Adam
Forgot to post my current code.
Code: Select all
$calDate1 = strtotime($calDate);
$calDateA = date('j M',$calDate1);
$calMonth = date('m',$calDate1);
$calMonthA = date('F',$calDate1);
if ($i!=0) {
$calPreviousMonth1 = mysql_result($result,$i-1,"calDate");
$calPreviousMonth2 = date('m',$calPreviousMonth1);
if ($calMonth!=$calPreviousMonth2){
echo "<p>$calMonthA</p>";
}
} else {
echo "<p>$calMonthA</p>";
}Maybe something like this...
Didn't know your column names so I just made up some...
pif!
Didn't know your column names so I just made up some...
Code: Select all
$r = mysql_query ( "SELECT DATE_FORMAT(newsDate, '%m-%d-%Y') AS newsDate, DATE_FORMAT(newsDate, '%M') AS headerDate, newsHeadline, newsURL, newsText FROM news ORDER BY newsDate ASC" );
if ( mysql_num_rows ( $r ) > 0 )
{
$data = array ();
While ( $x = mysql_fetch_array ( $r, MYSQL_ASSOC ) )
{
if ( ! isset ( $data[$x['headerDate']] )
{
$data[$x['headerDate']] = '';
echo "<p class='newsmonth'>" . $x['headerDate'] . "</p>";
}
echo "<p class='news'>" . $x['newsDate'] . " <a class='newshead' href='" . $x['newsURL'] . "'>" . $x['newsHeadline'] . "</a><br />" . $x['newsText'] . "</p>";
}
}pif!
Hurrah, I figured it out! Inserted
just before the end of the 'while' section, so that when it goes through for the next record it compares $calMonth to the $calprevMonth in its memory - simple really, duh@me
Thanks again to feyd and thanks to printf - figured it out before I'd read your post, but I'll take a look at your code too, because it means nothing to me at present and there's so much to learn and benefit from!
Code: Select all
$calprevMonth = "$calMonth";Code: Select all
$calDate1 = strtotime($calDate);
$calDateA = date('j M',$calDate1);
$calMonth = date('m',$calDate1);
$calMonthA = date('F',$calDate1);
if ($i!=0) {
if ($calMonth!=$prev){
echo "<p>$calMonthA</p>";
}
} else {
echo "<p>$calMonthA</p>";
}