[PHP] Grouping years for display

[Adnams Drinker]

Probably just cause it's Friday and I'm knackered, but this has got me stumped ...

A client wants to show a list of the awards they've received over various years, and they want to be able to add future awards themselves, so the MySQL database fields I'm using consist of:

awardid (primary key)
year (stored as a year object in MySQL)
title (the name of the award - varchar object, max. 100 characters)
link (optional field containing a link to a detail page - varchar, max. 25 characters)

My SELECT query is:

SELECT awards.`year`, awards.title, awards.link FROM awards ORDER BY awards.`year` ASC

When I display them on the page I'm using the following:

<?php do { ?>
     <h2 class="pr"><?php echo $row_rsAwards['year']; ?></h2>
     <p><?php echo $row_rsAwards['title']; ?></p>
     <?php if ($row_rsAwards['link'] != "None") {echo '<p align="right"><a href="'.$row_rsAwards['link'].'">Show me the development &gt;</a></p>';} ?>
      <?php } while ($row_rsAwards = mysql_fetch_assoc($rsAwards)); ?>

It works fine in that it displays the data correctly in the format that I want, apart from the fact that for one year they got two awards. The <h2> head repeats, which looks a bit naff.

Is there any any simple way of stopping the <h2> from repeating if two or more consecutive records retrieved from the database have the same year - the idea being that the year heading in the <h2> will appear only once? Might not have explained that very well, so this might be clearer:

<h2>2001</h2>
((name of award))
((name of award))

<h2>2003</h2>
((name of award))

<h2>2004</h2>
((name of award))

I'm sure this is easier than I'm making it out to be - it's time I wasn't here and my brain has given up for the day ... any ideas or suggestions?

 

[Sic]

SELECT GROUP_CONCAT(title) AS title, year FROM awards ORDER BY year GROUP BY year

I can never remember if group by goes before order by or after!!

 

[Beansprout]

SELECT foo, shizzle
FROM bar
WHERE shizzle = theshiz
GROUP BY foo
HAVING foo > notsofoo
ORDER BY shizzle desc
LIMIT offset, rows



Take these fields from a table where this field meets a condition, then group them by another field where that field's value meets a condition, then order the result, then specify how many rows to send back.

Something like that anyway o.o


Edit: as for the OP, you could select all the rows in year order and put the results into a 2D associative array of $array[year][award], then output the array.

Or you could select all the rows matching each year and output them then move on to the next year, but that would need multiple SQL statements and a way to know which years there are (yet another query)

 

[Adnams Drinker]

We went away for the weekend soon after I posted the problem and I gave it some thought before reading your replies when we got home last night.

That'll teach me to try and write code late on a Friday afternoon when my brain has already convinced itself the weekend has started. I worked it out in the end and it was quite straightforward - where I loop through the returned results and output them to the screen, I've now got this:

<?php
	$prevyear = 0;
	$currentyear = 0;
		do { 
			$currentyear = $row_rsAwards['year'];
			if ($prevyear !== $currentyear) {
				echo '<h2 class="pr">'.$row_rsAwards['year'].'</h2>';
			}
                	echo '<p>'.$row_rsAwards['title'].'</p>';
                	$prevyear = $row_rsAwards['year'];
                	if ($row_rsAwards['link'] != "None") {
				echo '<p align="right"><a href="'.$row_rsAwards['link'].'">Show me the development &gt;</a></p>';
					}
                } while ($row_rsAwards = mysql_fetch_assoc($rsAwards)); ?>

It might not be pretty or ultra-efficient, but it does what I need it to do and more importantly, I worked it out for myself

Thanks for taking the time though gents ...

EDIT: Wahey, 1000th post - only taken me four bloody years