Page 1 of 1

[SOLVED] Displaying Archives

Posted: Sat Apr 05, 2008 3:14 pm
by lafever
I'm trying to create an archive system for my blog and I want to know if I have the right query setup. I am having troubles figuring out how to get this to display both ways (MySQL and PHP).

I've been reading the MySQL manual on their website and I've come up with this as my query:

Code: Select all

SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(id) as `posts` FROM blog_posts  GROUP BY YEAR(date), DATE_FORMAT(date, '%d') ORDER BY date DESC

The way I'm trying to get it to display is like (I will worry about the counts at a later time once I get this working):

2008 (1)
-January (1)
--1 - Article 1
2007 (2)
-December (2)
--25 - Article 1
--25 - Article 2


Now I can get them to display in PHP ....somewhat. I'm still not too well with loops and what not and getting data into arrays. Here is what I currently have.

for MySQL dump

Code: Select all

 
 
INSERT INTO `blog_posts` (`id`, `author`, `date`, `post_content`, `post_title`, `post_category`, `comment_status`, `post_modified`, `post_link`, `post_keywords`, `post_views`, `comment_count`) VALUES 
(1, 1, '2008-04-01 13:50:35', 'testy', 'This will be the title of the post.', 0, 'publish', 'closed', '2008-04-01 13:50:35', 'this-will-be-the-title-of-the-post', 'welcome', 0, 0),
(2, 1, '1993-03-15 18:09:07', 'testing', 'testing', 1, 'open', '1993-04-15 18:09:07', 'testing', 'test', 0, 0),
(3, 1, '1984-04-09 18:13:31', 'test', 'test', 1, 'open', '2008-04-09 18:13:47', 'tes', 'tes', 0, 0),
(4, 1, '2008-04-04 18:35:21', 'ettertertt', 'testtt', 0, 'open', '2008-04-04 18:35:21', 'testtt', 'rtetret', 0, 0),
(5, 1, '1993-04-14 23:40:37', 'test', 'test', 1, 'open', '2008-04-30 23:40:51', 'tewtet', 'wetwet', 0, 0);
 

For PHP

Code: Select all

<?php
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(id) as `posts` FROM blog_posts  GROUP BY YEAR(date), DATE_FORMAT(date, '%d') ORDER BY date DESC", $connection);
 
 
    for ($n = 1; $n <= mysql_num_rows($results); $n++ ) {
      $row = mysql_fetch_array($results);
      echo $row['year'].' - '.$row['month'].' - '.$row['posts'].'<br />';
    }
 
?>

These are my results:
2008 - 04 - 1
2008 - 04 - 1
1993 - 04 - 1
1993 - 03 - 1
1984 - 04 - 1


I've kind of got the idea going but I don't know how to fully get it going. My page will be setup 'archive.php?yr=&mon=&title='

I basically want it to view as a tree

-If nothing is set I'll have a collapsed display
2008 (2)
1993 (2)
1984 (1)
-If year is set (ex; 2008) i'll have a display like so
2008 (2)
-April (2)
--DATE - TITLE
--DATE - TITLE
1993 (2)
1984 (1)

And so on.....

I desperately need some help with this. I have been messing with this for 2 days and it didn't take me this long to figure out how to do categories.

Can this all be done with my single SQL statement or do I need to make different ones for each? How can I get these loops to stop displaying after it already posted that year? How can I get it to indent if the ['yr'] is set and display the months with indentions? Same with dates and titles if ['yr'] and ['mon'] is set

Re: Displaying Archives

Posted: Sat Apr 05, 2008 9:27 pm
by John Cartwright

Code: Select all

<?php
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(id) as `posts` FROM blog_posts  GROUP BY YEAR(date), DATE_FORMAT(date, '%d') ORDER BY date DESC", $connection);
 
$categories = array();
while ($row = mysql_fetch_assoc($results)) {
   $category= $row['year'] .' '. $row['month'];
   if (!isset($categories[$category])) {
      $categories[$category] = array();
   }
   $categories[$category][] = $row;
} 
 
foreach ($categories as $title => $element) {
   echo '<h1>'. $title .'('. count($element) .')</h1>';
   foreach ($element as $row) {
      echo $row['year'].' - '.$row['month'].' - '.$row['posts'].'<br />';
   }
}
 
?>
I usually don't like to write code, but I see this question come up frequently enough. My preference is to store the rows into an array, which can be classified by different keys (in your case month and year). As shown above, it allows allows you to easily count the number of elements in each category.

Re: Displaying Archives

Posted: Sat Apr 05, 2008 11:37 pm
by lafever
Wow, I really wasn't expecting this. I have received zero help at a couple of places, not even any tips on how to do it. Thank you so much. I should be able to figure it out how to format it into the format I'd like it from here.

I see why my foreach statement kept giving me an error now when I was trying to do it that way. I wasn't setting an array at all I was just trying to call the foreach statement.

I think I found myself a new home for forums :drunk:


EDIT: No resolved buttons here?

Re: Displaying Archives

Posted: Sun Apr 06, 2008 7:42 am
by John Cartwright
EDIT: No resolved buttons here?
Unfortunately not. We ask our users to modify the title in their original post to include [SOLVED]

I'm pleased you are enjoying your stay at devnet ;)

Re: Displaying Archives

Posted: Sun Apr 06, 2008 7:59 am
by lafever
Alright, Will do. Just one more quick question.

I know I'm going to need another loop to do what I want to do or maybe I can't do all this on one query. I've been playing with the code throughout the day and I'm *almost* to where I need it to be. I'm trying to get it to display into somewhat of a 'tree' and this is what I have.

Code: Select all

<?php
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(id) as `posts`, post_title as `title` FROM blog_posts  GROUP BY YEAR(date), DATE_FORMAT(date, '%d') ORDER BY date DESC", $connection);
 
$posts = array();
 while ($row = mysql_fetch_assoc($results)) {
    $post = $row['year'];
    if (!isset($posts[$post])) {
       $posts[$post] = array();
    }
    $posts[$post][] = $row;
 }
 
foreach ($posts as $year => $count) {
    echo '<strong>'.$year .' ['. count($count) .']</strong><br />';
    foreach ($count as $row) {
        echo '&nbsp;&nbsp;'.$row['month'].'['.count($row['month']).']<br />';
        echo '&nbsp;&nbsp;&nbsp;&nbsp;'.$row['day'].' - '.$row['title'].'<br />';
      }
    }
?>

My results are:

Code: Select all

 
2008 [2]
  04[1]
    04 - testtt
  04[1]
    01 - This will be the title of the post.
1993 [2]
  04[1]
    14 - test
  03[1]
    15 - testing
1984 [1]
  04[1]
    09 - test
 

The while() loop I tried adding in wasn't displaying the way I wanted to so I just deleted it.

Thanks again for all of the help again.

Re: Displaying Archives

Posted: Sun Apr 06, 2008 8:12 am
by John Cartwright
Oh okay I see where you are going with this. I didn't realize you wanted to organize the year and month seperately into their own classification (I thought you just grouped them together). What you need to do then is add another dimension to your array.

Code: Select all

$posts = array();
while ($row = mysql_fetch_assoc($results)) {
    if (!isset($posts[$row['year']][$row['month']])) {
       $posts[$row['year']][$row['month']] = array();
    }
    $posts[$row['year']][$row['month']][] = $row;
}
 
foreach ($posts as $year => $yelement) {
    echo '<div class="tree_title">'.$year .' ['. count($yelement) .']</div>';
    foreach ($element as $month => $melement) {
        echo '<div class="tree_month">'. $month .' [ '. count($melement) .']</div>';
        foreach ($melement as $row) {
            echo '<div class="tree_row"> '. $row['day'].' - '.$row['title'].'</div>';
        }
    }
}
If you are creating a tree'sk type of list, I would either use div's or nested lists to achieve the effect.

Re: Displaying Archives

Posted: Sun Apr 06, 2008 8:31 am
by lafever
Yes yes. I was reading on the multi-dimensional arrays earlier but I wasn't preparing it right.

Just a little fix to it though for anyone who needs help later on.

Line 11 on your post:

Code: Select all

foreach ($element as $month => $melement) {
Should be:

Code: Select all

foreach ($yelement as $month => $melement) {
You have been a significant help. Thank you.

For some reason though the first year doesn't display the right amount for the count but the months on the year do.. I think it's something with the query. I'll keep posted when I figure it out.

I definitely have to read more on multi-dimensional arrays because I ALMOST had a setup like this earlier but I was using for loops instead of foreach (don't ask why, haha).

Re: [SOLVED] Displaying Archives

Posted: Mon Apr 07, 2008 3:48 am
by lafever
Also, there's a little issue with the query in this I figured out.

My counts were not right and it was not displaying everything. Here is the correct Query if anyone needs it for future reference.

Code: Select all

 
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(*) as `posts`, post_link as `link`, post_title as `title`
FROM blog_posts $where GROUP BY date ORDER BY date DESC", $connection);