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