Jump to content

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


vynsane

Recommended Posts

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.

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 />';
}

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.