PHP/MySQL group news/archives by year then month

Question

Hi,

I've got some code which outputs a news archive sidebar. It all works very well apart from the fact that it doesn't split the months by year properly. Rather than assigning the relevant month to its year, all months are being shown for all years! Very frustrating! Any help would be greatly appreciated.

$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[$timePeriod]))
                        {
                        $newsArray[$timePeriod] = array();
                        }       
                        $newsArray[$timePeriod][] = $newsResult;                            
                        }                           

                        //by year
                        foreach ($newsArray as $timePeriod => $newsItems)
                        {   
                        $timePeriodY = date('Y',strtotime($timePeriod));
                        echo '<li><strong>' . $timePeriodY . '</strong>' . PHP_EOL;  
                        echo '<ul>' . PHP_EOL;

                        //by month
                        foreach ($newsArray as $timePeriod => $newsItems)
                        {                                   
                        echo '<li><strong>' . $timePeriod . '</strong>' . PHP_EOL;  
                        echo '<ul>' . PHP_EOL;                      

                        //news items
                        foreach ($newsItems as $item)
                        {
                        echo '<li>';
                        echo '<a href="'.$wwwUrl.'press-releases/'.$item["id"].'/'.$item["title"].'.php">'.$item["title"].'</a>';
                        echo '</li>' . PHP_EOL;
                        }   

                        //end by month
                        echo '</ul>' . PHP_EOL; 
                        echo '</li>' . PHP_EOL;             
                            }

                            //end by year
                        echo '</ul>' . PHP_EOL; 
                        echo '</li>' . PHP_EOL;             
                            }

                            echo '<li>&nbsp;</li>' . PHP_EOL;   
                            echo '</ul>' . PHP_EOL; 
                        }   

                        else
                            {
                        echo 'We currently have no press releases available';
                        }

Many thanks in advance S

Answer 1

You need to be using :

ORDER BY abc, xyz DESC

Answer 2

Why You dont just Use

ORDER BY date DESC

Answer 3

When you're printing, why are you iterating through the $newsArray twice (when you have the foreach within foreach)? Maybe I'm missing something, but basically for every month/year in the array you are printing all the news for all the months/years. Keeping just the inner foreach should solve the problem.