[SOLVED] group by date?

[delphi123]

I've got a very basic news posting system which is storing posts in a mysql table.

 

It currently stores the date automatically and I'm planning to make a news archive for when it disappears off the front page.

 

I reckon I could work out how to manually enter each month and show just news items matching that month, however can anyone tell me if there's a clever way to generate my news archive?

 

I'd like to have the articles grouped by year and month, ie it'd appear on the webpage something like:

 

2007

  December

    Article 1

    Article 2

    Article 3

    Article 4

    Article 5

  November

    Article 6

    Article 7

    Article 8

    Article 9

 

2006

  July

    Article 10

    Article 11

    Article 12

[obsidian]

Is your date field and actual DATE type in the database? If so, you can do something like this:

 

<?php
$sql = mysql_query("SELECT * FROM my_articles");
$archive = array();
if (mysql_num_rows($sql) > 0)
{
  // We have records
  while ($row = mysql_fetch_assoc($sql))
  {
    $ts = strtotime($row['date_col']);
    list($year, $month) = explode('-', date('Y-F', $ts));
    $archive[$year][$month] = $row;
  }
}

foreach ($archive as $year => $months)
{
  echo "<h1>$year</h1>\n";
  foreach ($months as $month => $art)
  {
    echo "<h2>$month</h2>\n";
    echo "<ul>\n";
    foreach ($art as $record)
    {
      echo "<li>{$record['title']}</li>\n";
    }
    echo "</ul>\n";
  }
}
?>

 

Hope that helps point you in the right direction.

[delphi123]

Hi Obsidian,

 

You're a flippin genius!  Never would have had a chance of working that out! 

 

I'm not exactly sure what you mean by actual DATE type?

 

I've got a column called 'date' and it is set to datetime - is that what you mean?

 

Anyway I've updated all the fields required and it's displaying this:

 

Recent News Articles

2007

November

 

    * 2

    * N

    * N

    * s

    * 1

    * h

    * 2

 

So it's sort of working, however I'm struggling to understand your foreach bit where it's calling $variables - I've pasted my sql request in the code below - have I missed the point completely?!  :-\

 

$query = ("SELECT news_id, category, title, text, img_url, linkto, DATE_FORMAT(date,'%d %M, %Y') as sd FROM news_posts WHERE category='news' ORDER BY date DESC LIMIT 4 ") or DIE ("Can't retrieve");

$result = @mysql_query($query);
?>

	<?php
		$sql = mysql_query("SELECT * FROM news_posts");
		$archive = array();
			if (mysql_num_rows($sql) > 0)
				{
					 // We have records
					 while ($row = mysql_fetch_assoc($sql))
					 {
				   $ts = strtotime($row['date']);
  				  list($year, $month) = explode('-', date('Y-F', $ts));
   					 $archive[$year][$month] = $row;
  					}
				}

			foreach ($archive as $year => $months)
			{
				  echo "<h1>$year</h1>\n";
  				foreach ($months as $month => $title)
  				{
  				  echo "<h2>$month</h2>\n";
   				 echo "<ul>\n";
  				  foreach ($title as $record)
   				 {
    			  echo "<li>{$record['title']}</li>\n";
  				  }
   				 echo "</ul>\n";
  				}
			}
			?>

[obsidian]

Yes, the DATETIME is exactly what I was asking ... OK, with the query you posted, I'd recommend something like this:

 

<?php
$q = "SELECT news_id, category, title, text, img_url, linkto, date, DATE_FORMAT(date,'%d %M, %Y') as sd FROM news_posts WHERE category='news' ORDER BY date DESC LIMIT 4";
$sql = mysql_query($q);
$archive = array();
if (mysql_num_rows($sql) > 0)
{
  // We have records
  while ($row = mysql_fetch_assoc($sql))
  {
    $ts = strtotime($row['date']);
    list($year, $month) = explode('-', date('Y-F', $ts));
    $archive[$year][$month][] = $row;
  }
}

foreach ($archive as $year => $months)
{
  echo "<h1>$year</h1>\n";
  foreach ($months as $month => $art)
  {
    echo "<h2>$month</h2>\n";
    echo "<ul>\n";
    foreach ($art as $record)
    {
      echo "<li>{$record['title']} - posted {$record['sd']}</li>\n";
    }
    echo "</ul>\n";
  }
}
?>

 

How does that work for you?

[delphi123]

:o :o Brilliant!!  :o :o

 

That's saved me so much time!  Thank you so much for your help obsidian!

 

 

[obsidian]

:o :o Brilliant!!  :o :o

 

That's saved me so much time!  Thank you so much for your help obsidian!

 

Glad to have helped. That's what we're here for!

[dvayne]

1969

  December

    news....

 

year and month is wrong, please help me...

how do I get the year and month of the news that is stored in the database?