[SOLVED] Displaying Archives
Posted: Sat Apr 05, 2008 3:14 pm
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:
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
For PHP
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
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 DESCThe 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