PHP/MySQL news archive

Question

Hi,

I'm a bit stuck trying to get my code to output correctly, see below. It all works ok, but rather than displaying all news items, it only shows one for each month. What I need to do is group all news for a selected month with the month/year heading for that month. Hope this makes sense.

Any help greatly appreciated. SS

$theQuery="Select * from isnews WHERE active = '1' GROUP BY YEAR(date) DESC, MONTH(date) ORDER BY YEAR(date) DESC, MONTH(date) DESC";       
   $newsQuery=mysql_query($theQuery);      
   if(mysql_num_rows($newsQuery)>0) {      
   while ($newsResult=mysql_fetch_array($newsQuery)) {        
     $newDate =  $newsResult['date'] ;             
     echo '<div class="date">' . date('F  Y ',strtotime($newDate)) . '</div>';         
     echo '<ul class="press">';        
     echo '<li>
     <img src="'.$wwwUrl.'images/news/'.$newsResult['image'].'" width="'.$newsResult['tnWidth'].'" height="'.$newsResult['tnHeight'].'" title="'.$newsResult['title'].'" alt="'.$newsResult['title'].'" />
     <h3><a href="press-releases/'.$newsResult["id"].'/'.$newsResult["title"].'.php">'.$newsResult["title"].'</a></h3>         
     '.substr($newsResult['descrip'],0,100).'
     <p><a href="press-releases/'.$newsResult["id"].'/'.$newsResult["title"].'.php">Read more</a></p> 
     </li>';
   }
   echo '</ul>';
   } else {
   echo 'We currently have no press releases available';
   }  

Answer 1

it only shows one for each month

That's correct, your GROUP BY isn't correct. All columns (see the * ) should be in your GROUP BY because all of them are in the SELECT and you don't use any aggregate function. MySQL has very strange behaviour and now only returns the first record it can find.

If you want all records, just drop the entire GROUP BY, and ORDER BY the month and year to get the correct sort order. In youw PHP you can make some groups, but that has nothing to do with SQL.

You might consider ONLY_FULL_GROUP_BY, this helps to prevent strange/false results.

Answer 2

There are two problems that I can see. First of all, GROUP BY is an aggregate function, so it is used to combine multiple rows into one row in your result (for instance, if you wanted to see how many news items were written for a given month and year). Secondly, even if you were getting multiple records per time period, you are outputting a date header for every record that you pull from the database (ie. you would get duplicate headers if you have multiple news items from the same month and year).

A better solution would be to collect all your active news items (without the GROUP BY clause), and then build an array which you can then iterate over to output your page:

$query = "SELECT *
  FROM isnews
  WHERE active = '1'
  ORDER BY YEAR(date) DESC, MONTH(date) DESC";
$resultSet = mysql_query($query);

if (mysql_num_rows($resultSet))
{
  $newsArray = array();
  while ($newsResult = mysql_fetch_array($resultSet))
  {
    // The time period is what you will output as your header
    $timePeriod = intval(date("F Y", $newsResult['date']));

    if (!isset($newsArray[$timePeriod]))
    {
      // Create a subarray if needed
      $newsArray[$timePeriod] = array();
    }

    $newsArray[$timePeriod][] = $newsResult;
  }

  foreach ($newsArray as $timePeriod => $newsItems)
  {
    echo '<div class="date">' . $timePeriod . '</div>';         
    echo '<ul class="press">';

    foreach ($newsItems as $item)
    {
     echo '<li>';
     // ... do your news item outputting
     echo '</li>';
    }

    echo '</li>';
    echo '</div>';
  }
}
else
{
  echo 'We currently have no press releases available';
}

Answer 3

Hi,

Many thanks for the help.

I've tried adapting the code from Daniel a little in order to create a sidebar-type archive list showing years, then the months and their corresponding news results. The end result being an accordion type menu where the years dropdown to show the months, then the months dropdown to display the news items. I can get it to work as far as the years go but can't seem to get the months working properly.

Any pointers/help more than greatly appreciated. (code below) SS

$query = "SELECT * FROM isnews WHERE active = '1' ORDER BY YEAR(date) DESC, MONTH(date) DESC";
                        $resultSet = mysql_query($query);

                        if (mysql_num_rows($resultSet))
                        {
                        $newsArray = array();

                        echo '<ul>' . PHP_EOL;
                        echo '<li><strong>Press releases:</strong></li>' . PHP_EOL; 

                        while ($newsResult = mysql_fetch_array($resultSet))
                        {                                   
                        $newDate =  $newsResult['date'] ;   
                        $timePeriod = date('F  Y ',strtotime($newDate));
                        $timePeriodY = date('Y',strtotime($timePeriod));
                        $timePeriodM = date('F',strtotime($timePeriod));

                        if (!isset($newsArray[$timePeriodY]))
                        {
                        $newsArray[$timePeriodY] = array();
                        }
                        $newsArray[$timePeriodY][] = $newsResult;                           
                        }
                        foreach ($newsArray as $timePeriodY => $newsItems)
                        {                                                                                           
                        echo '<li><em>' . $timePeriodY . '</em>' . PHP_EOL;  
                        echo '<ul>' . PHP_EOL;

                        foreach ($newsItems as $item)
                        {
                        echo '<li>';
                        echo '<a href="press-releases/'.$item["id"].'/'.$item["title"].'.php">'.$item["title"].'</a>';
                        echo '</li>' . PHP_EOL;
                        }                           
                        echo '</ul>' . PHP_EOL; 
                        echo '</li>' . PHP_EOL;             
                            }
                            echo '</ul>' . PHP_EOL; 
                        }
                        else
                            {
                        echo 'We currently have no press releases available';
                        }