[SOLVED] Help optimizing news page code - get rid of multiple query/while loops

[vynsane]

I'm trying to re-write some of my pages now that I'm more familiar with complex queries. One such page is my news page:

 

www.WallCrawlersWeb.com/news

 

Right now the way the page is coded, I query for distinct dates, then have a while loop for the dates, THEN query for articles matching that date. I'm sure there's a more efficient way of handling this, but I can't get it to work. I've been trying to use a foreach inside the while, using only one query, but I just can't wrap my head around the foreach function.

 

A simplified version of what I have now is:

 

$newsQuery = ("SELECT DISTINCT date FROM News");
  while($dates = mysql_fetch_array($newsQuery)) {
      echo "$dates['date']";
      $articlesQuery = ("SELECT * FROM News WHERE date = '".$dates['date']."'");
          while($articles = mysql_fetch_array($articlesQuery)) {
               echo "article stuff";
           }
     }

 

Like I said, I'm trying to optimize my code by writing less queries to get the same result, and I want to get this into one query, and I think I need to use a foreach loop to spit out the articles FOR EACH date. In the abstract, that makes perfect sense to me, I just need a better understanding of the foreach function and how to implement it. I've tried checking the documentation and it's just not clear enough to me.

[hitman6003]

This will create a multidimensional array of all the articles, sorted by date, with the date being the first key...

 

$query = "SELECT * FROM News ORDER BY date DESC";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
  $articles[$row['date']][] = $row;
}

echo '<pre>' . print_r($articles, true) . '</pre>';

 

To print articles:

 

foreach ($articles as $date => $articles) {
  echo 'Articles for ' . $date . ':';
  foreach ($articles as $article) {
    echo '<a href="' . $article['link'] . '">' . $article['title'] . '</a>';
  }
  echo '<br /><br />';
}

[vynsane]

Awesome. Thanks much, that worked! I'm beginning to get more of a sense of the foreach() function... I'll get there.