Using PHP to group and display MySQL entries by week

Question

Hi - I'm building a site with several 'one-liners'. These are added using a simple PHP5 form, stored in a MySQL 5 database. Each row has an 'id' (auto_increment), 'title', 'description', 'status' (1 or 0) and 'date_added' (MySQL datetime).

I want to display them grouped by Month and Year, like so:

<dl>
    <dt>August 2009</dt>
    <dd><strong>title 1</strong> - description 1</dd>
    <dd><strong>title 2</strong> - description 2</dd>
    <dd><strong>title 3</strong> - description 3</dd>         
    etc...
</dl>
    <dl>
    <dt>July 2009</dt>
    <dd><strong>title 1</strong> - description 1</dd>
    <dd><strong>title 2</strong> - description 2</dd>
    <dd><strong>title 3</strong> - description 3</dd>         
    etc...
</dl>

I found a MySQL snippet which ostensibly groups rows by month, however the query only returns a couple of results, rather than the full table:

SELECT `title`, `description`, `date_added`
FROM one_liners WHERE `status`=1 GROUP BY MONTH(date_added)

How would I go about grouping them, and then looping to display as above?

Any help would be greatly appreciated.

Thanks!

Answer 1

I think you'll need to do this in PHP alone. Group-by in SQL is usually used for things like SUM, AVG, MAX, MIN, COUNT, etc. It doesn't do anything to the actual record set to show some kind of grouping that you can access in PHP.

What I've done in situations like this is a variable to track with within the loop to know when I get to a new group. For your SQL you'll do:

SELECT `title`, `description`, `date_added`
FROM one_liners WHERE `status`=1 ORDER BY MONTH date_added DESC

Then in the PHP you'll do:

$prevMonth = '';
while ($row = mysql_fetch_assoc($result)) {
    $currMonth = $row['date_added'];
    if ($currMonth != $prevMonth) {
        if ($prevMonth != '') {
            echo '</dl>';
        }
        echo '<dl><dt>' . $currMonth . '</dt>';
        $prevMonth = $currMonth;
    }
    echo '<dd><strong>' . $row['title'] . '</strong>';
    echo ' - ' . $row['description'] . '</dd>';
}
if (mysql_num_rows($result) > 0) {
        echo '</dl>';
}

All this is really doing it keeping track of the month from the previous row as you loop through. When you get to a new one it will close the old DL and open a new one.

Depending on how you store your dates in the DB (timestamp vs datetime) you'll need to replace $currMonth = $row['date_added']; with the appropriate code to convert it to the format "Aug 2009" or whatever you want.

Answer 2

You don't want to use a GROUP BY clause for this. Though it "sounds" like what you want, it has completely different implications.

Run a normal query:

SELECT `title`, `description`, `date_added` FROM one_liners 
WHERE `status`= 1 ORDER BY `date_added`

Then loop the results, doing something like this:

$query = 'SELECT `title`, `description`, `date_added` FROM one_liners WHERE `status`= 1 ORDER BY `date_added`';
$res = mysql_query( $query );
$month = null;
$year  = null;

echo '<dl>';
while($row = mysql_fetch_assoc( $res ){
  $r_month = strtotime( $row['date_added'] );
  if($month != date('m', $r_month) || $year != date('Y', $r_month)){
     echo '<dt>' . date('F Y', $r_month) . '</dt>';
     $month = date('m', $r_month);
     $year  = date('Y', $r_month);
  }
  echo '<dd><strong>' . htmlentities( $row['title'] ) . '</strong> &mdash;' . htmlentities( $row['description'] ) . '</dd>';
}
echo '</dl>';

I specifically chose to use only one dl because a dl is supposed to hold many dd and dt elements, not just one pair per list. Feel free to adjust my code as needed if you disagree :)

Answer 3

You don't want to be using GROUP BY there, try using ORDER BY instead.