CODEWITHSUNDEEP
phpmysql
Stephan Hovnanian
Stephan Hovnanian

Reputation: 189

how to correctly group/sort a mysql table

I need some help understanding some "next level" mysql/php commands, beyond just simple query statements and looping through arrays.

I have a table that contains publications written by various faculty members. They're all stored together, and there's a faculty_id column that tells me which publication belongs to which faculty member. On a "Bio" web page I want to retrieve these publications...no problem right? The issue I'm having is how to display them: grouped by the 'year' column with the year as a heading. So, for example, the output would look like this:

2010

2009

2007 // assuming there was no pub in 2008

Here's where I started, what else can I do to make this more efficient? Or is there a way to create a multi-dimensional array from this query below using PHP?

"SELECT bibliography,year,link,position FROM faculty_pubs WHERE faculty_id='$faculty_id' AND status=1 ORDER BY year DESC, position ASC"

Upvotes: 0

Views: 531

Answers (2)

Aaron W.
Aaron W.

Reputation: 9299

Well it doesn't look like you need to group by anything. You can create an array of publications based on year in php by going through the MySQL results. Not tested one bit, but I hope you get that idea. You can do this within the query parsing like Marc B shows as well...which may be better depending on the number of publications we're talking about.

$publications = array();
$q = mysql_query("SELECT `bibliography`,`year`,`link`,`position` FROM `faculty_pubs` WHERE `faculty_id`='$faculty_id' AND `status`=1 ORDER BY `year` DESC, `position` ASC");
while($r = mysql_fetch_assoc($q)) {
 // separate publication out by year
 $publications[$r['year']][] = $r;
}

// no need to sort the array since all the results were put in order by the SQL sorting
foreach($publications as $year => $pub) {
 echo '<p>'.$year.'</p>';
 echo '<ul>';
 foreach($pub as $p) {
  echo '<li><a href="'.$p['link'].'">Publication title or position here</a></li>';
 }
 echo '</ul>';
}

Upvotes: 1

Marc B
Marc B

Reputation: 360662

If you're storing that in a single flat table, then displaying them by year is a purely "display" problem. Your query would spit out results looking like:

| bibliography | year | link       | position |
-----------------------------------------------
| blah blah    | 2008 | http://... | ???      |
| blah blah    | 2009 | http://... | ???      |
| blah blah    | 2009 | http://... | ???      |
| blah blah    | 2010 | http://... | ???      |
etc...

You'd simply loop over the results, keep track of the year, and whenever a new year record shows up, do whatever formatting you need (new <ul>, new table, whatever...).

$previous_year = null;
while($row = mysql_fetch_assoc()) {
     if ($previous_year != $row['year']) {
         // start a new year
     }
     ... display publication
     $previous_year = $row['year']
}

Upvotes: 1

Related Questions