Page 1 of 1

Dividing results with 'group' headings

Posted: Tue Jun 13, 2006 3:12 pm
by fissssssi
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!

Posted: Tue Jun 13, 2006 3:34 pm
by feyd
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.)

Posted: Tue Jun 13, 2006 5:29 pm
by fissssssi
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.

Code: Select all

{group heading(1)}
  {result 1}
  {result 2}
{group heading(2)}
  {result 3}
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

Posted: Tue Jun 13, 2006 6:44 pm
by feyd
As I said in the posts I was referencing, track the previous month. Compare it with the current month. If they differ, it's a new month, so output the heading.

Posted: Wed Jun 14, 2006 1:25 pm
by fissssssi
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!

Posted: Wed Jun 14, 2006 2:05 pm
by fissssssi
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++;
}

Posted: Wed Jun 14, 2006 4:19 pm
by feyd
it's the general direction, yes.

Posted: Sun Jun 18, 2006 7:44 am
by fissssssi
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

Posted: Sun Jun 18, 2006 7:48 am
by fissssssi
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>";
}

Posted: Sun Jun 18, 2006 10:50 am
by printf
Maybe something like this...

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!

Posted: Sun Jun 18, 2006 11:34 am
by fissssssi
Hurrah, I figured it out! Inserted

Code: Select all

$calprevMonth = "$calMonth";
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

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>"; 
}
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!