[RESOLVED] Trying to create blog like archive links

GIJew

I'm working on a journal application that creates a sidebar similiar to that of a blog that should group by month and year.

Perhaps I'm too tired or perhaps I've been working too much today but this one is beyond me right now. I'm getting weird results and this looks like it should work for me. Anyhoo, I'm using AdoDb to pull out the data so just an FYI

From what I know I know that this should be grouping the years and the months correctly but as you can see from the results it isn't. It actually looks like I'm outputting the first row successfully and the rest of the data is coming out as a piece of an array...

Thanks for any help you can provide me!

Database field `created_on`
Data example: 2008-04-03 01:01:01
              2008-04-01 12:13:14
              2008-01-14 03:04:05
              2007-12-25 12:12:12

<?php
// PHP Function
Function getArchives () {
  global $db;
  $SQL = $db->GetAll("SELECT DISTINCT YEAR (created_on) AS `year`, MONTH(created_on) AS `month` FROM journal_categories GROUP BY YEAR(created_on), MONTH(created_on) ORDER BY created_on DESC");
  If (Count($SQL) != 0) {
    ForEach ($SQL AS $REC) {
      $O .= $REC['month'].'-'.$REC['year'].'<br />';
    }
  }
  return $O;
}
?>

4-2008
4-8
1-8[/CODE]

GIJew

Just an FYI I was too lazy to copy the exact db table contents so the output isn't exactly the same. Using the data provided it would probably output:

4-2008
4-8
1-8
12-7

Just didn't want anyone to get me on that one at least 🙂

arty56

GIJew wrote:
I'm working on a journal application that creates a sidebar similiar to that of a blog that should group by month and year.

It actually looks like I'm outputting the first row successfully and the rest of the data is coming out as a piece of an array...

I assume you are wanting an archive nav with month, year and number of posts per month.

From my blog site:
Archives
January 2008 [ 2 ]
February 2008 [ 2 ]
March 2008 [ 1 ]
April 2008 [ 2 ]

PM if you want to see a possible solution.

Arty

GIJew

Hey Arty,

I tried sending you a PM but it will not let me. I would really, really like to see a possible solution to this one.

Thanks!

timmy0320

I would like to see your solution too arty. I'm having the 'somewhat' the same problems except I didn't make it as far as GIJew I just had the SQL syntax.

Hope you can help ! 🙂

Mine will be a little different but I can edit accordingly

2008 (2)
--April (1)
--- 1 - April Fools!
--March (1)
--- 30 - Hello!

timmy0320

I'm onto something. Slowly... foreach wasn't working so I decided to use a for statement.

<?php
    for ($n = 1; $n <= mysql_num_rows($results); $n++ ) {
      $row = mysql_fetch_array($results);
      echo $row['year'].'<br />';
    }

I have 4 entries in my database two posted in year 2008, one 1993, one 1984 and my result is

2008
1993
1984

I now need to get a count somehow next to the years of how many entries there are for that year.

I hope this helps you out a little too GIJew. I'll keep posting when I figure more stuff out.

GIJew

Hey Timmy,

Try doing a COUNT(id) as count in your query and that should return the amount of entries.

What does your query look like? I'm still not returning crap :/

GIJew

GOT IT!!!!

It's all in the query!

SELECT DISTINCT DATE_FORMAT(created_on,'%m-%Y') as dates FROM journal_categories GROUP BY created_on ORDER BY created_on DESC

After that just a foreach() loop using dates to display and I get the right output. HTH with your problem too Timmy!

arty56

GIJew wrote:
Hey Arty,

I tried sending you a PM but it will not let me. I would really, really like to see a possible solution to this one.

Thanks!

That's a pity as I wanted you to see what I wrote before giving you the code, just encase it wasn't what you were after.

Got pinged by the moderator, OOPs.

Basically what I did was created an archives db with just 3 fields:
ArchiveID,ArchiveDate and validate

ArchiveID << auto increment
ArchiveDate << varchar format [January 2008]
validate << tinyint default =0

Validate is here because I am lazy in having the field added as every new month and year arrives.
So I have added January 2008 to December 2008 in the db and set the validate=1 for the past and
present months to appear.

In my main db where all my posts are stored, [advert_rss], I added a field ArchiveID.
So now when I post a new comment I select the month from a drop down box which then adds the
archiveID to the post in advert_rss table.

To get the number of posts for any given month I use an inner join statement with
$totalrows = mysql_num_rows($result_count); as part of my while statement.

Just view the script. lol

<?php

$query = "SELECT ArchiveID from Archive where validate='1'";
$result = mysql_query($query) or die ("can not select from Archive");

//we add this line because we need to know the number of active Archive Months
$num_rows = mysql_num_rows($result);
echo "<table><tr>";
//changed this to a for loop so we can use the number of active Archive Months found
for($i = 0; $i < $num_rows; $i++) {//1
    $row = mysql_fetch_array($result);

$cat = stripslashes($row['ArchiveID']);

// The following query gets the Archive month and year and sets it up as the Headers

   $getmaincats="SELECT * from Archive where ArchiveID='$cat' order by ArchiveDate asc";
   $getmaincats2=mysql_query($getmaincats) or die("Could not get root Archive"); 

   //print "<tr>";
   while($row=mysql_fetch_array($getmaincats2))
   {//3
	$ArchiveID = stripslashes($row['ArchiveID']);
	$ArchiveDate = stripslashes($row['ArchiveDate']);
	$Archives = stripslashes($row['ArchiveDate']);

// The following query gets the number of Posts made per each Archive Month and prints next to the month. ie [2]

 $query_count = "SELECT * FROM Archive inner join advert_rss on Archive.ArchiveID = advert_rss.ArchiveID where Archive.ArchiveID = '$ArchiveID' order by advert_rss.ArchiveID ASC";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count); // Gets the number of articles per month

   print "<td><font face='arial' size=1><A href='http://www.yourdomain.com/blog/Archives/$ArchiveDates' target='MAIN'><b>$ArchiveDate</b></a> [ $totalrows ]</font></td></tr>";
  }//3
}//1
echo("</table>");
?>

Hope this helps anyone that thinks they need it.

Of course my code is not the tidiest so if you re-write it and it performs better, just post your code so we can all use.

I'm all for learning.

Arty

timmy0320

Yeah, I pretty much jacked mine all up. Back to square one. The foreach you use gives me an error.

arty56

timmy0320 wrote:
Yeah, I pretty much jacked mine all up. Back to square one. The foreach you use gives me an error.

That's odd as I checked the script before pasting it.

I'll find and post the script as a zip file.

Ok I found the script and all the code is the same as posted.
I ran the script again just to make sure it didn't error.

Whew it is ok.

Obviously you have to change the database details from mine to yours.

UMM, here's a thought give me your db details on what you're wanting and let me change the script for your needs.

Just a thought.

Here's a variation similar to your post above

January 2008 [ 2 ]
    Jan_1_2008
    Jan_2_2008
February 2008 [ 2 ]
    Feb_5_2008
    Feb_9_2008
March 2008 [ 1 ]
    Mar_5_2008
April 2008 [ 5 ]
    Apr_2_2008
    Apr_3_2008
    Apr_4_2008
    Apr_5_2008
    Apr_6_2008

<?php
include("your db connection script");
$query = "SELECT ArchiveID from Archive where validate='1'";
$result = mysql_query($query) or die ("can not select from Archive");
//we add this line because we need to know the number of active Archive Months
$num_rows = mysql_num_rows($result);
echo "<table><tr>";
//changed this to a for loop so we can use the number of active Archive Months found
for($i = 0; $i < $num_rows; $i++) {//1
    $row = mysql_fetch_array($result);
        $cat = stripslashes($row['ArchiveID']);
        $getmaincats="SELECT * from Archive where ArchiveID='$cat' order by ArchiveDate asc";
    $getmaincats2=mysql_query($getmaincats) or die("Could not get root Archive");
          //print "<tr>";
   while($row=mysql_fetch_array($getmaincats2))
   {//3
    $ArchiveID = stripslashes($row['ArchiveID']);
    $ArchiveDate = stripslashes($row['ArchiveDate']);
    $Archives = stripslashes($row['ArchiveDate']);
	$ArchiveDate = str_replace(" & ","_-_",$ArchiveDate);
	$ArchiveDate = str_replace("_"," ",$ArchiveDate);
	$ArchiveDates = str_replace(" ","_",$ArchiveDate);
// Print the Archive Date as the Header for each new month.
       print "<td><font face='arial' size=1><A href='http://www.yourdomain.com/blog/Archives/$ArchiveDates' target='MAIN' TITLE='View Posts by Month'><b>$ArchiveDate</b></a>";
     $query_count = "SELECT * FROM Archive inner join advert_rss on Archive.ArchiveID = advert_rss.ArchiveID where Archive.ArchiveID = '$ArchiveID' order by advert_rss.ArchiveID ASC";
    $result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count); // Gets the number of articles per month
// Print the total posts next to Header as [2]
print" [ $totalrows ]</font></td></tr>";
for($i = 0; $i < $totalrows; $i++) {//4
    $row = mysql_fetch_array($result_count);

$link = stripslashes($row['link']);
$title = stripslashes($row['title']);

$gettitle = stripslashes($row['title']);
$link= str_replace("-","_",$link);
$title = str_replace(" ","_",$title);
// Print the dates of each posts under the correct Month Header.
       print "<TR><TD>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='arial' size=1><A href='http://www.yourdomain.com/blog/$link/$title.html' TITLE='View Posts by Date Written'><b>$link</b></a></font></TD></TR>";
    }//4
  }//3
}//1
echo("</table>");
?>

You have to remember to change the db fields so they match what you are using.

to help you out here's a copy of the table structure for the tables I am using.

CREATE TABLE advert_rss (
id int(5) unsigned NOT NULL auto_increment,
ArchiveID int(5) NOT NULL,
title varchar(80) default '',
link varchar(200) NOT NULL default '',
comments varchar(256) NOT NULL,
description text,
keywords varchar(156) NOT NULL,
body blob NOT NULL,
author varchar(80) NOT NULL,
category varchar(80) NOT NULL,
guid varchar(156) NOT NULL,
sourceurl varchar(156) NOT NULL,
sourcelink varchar(156) NOT NULL,
pubdate varchar(50) default NULL,
imageurl varchar(120) NOT NULL default '',
imagetitle varchar(200) NOT NULL default '',
imagelink varchar(120) NOT NULL default '',
imp int(5) NOT NULL default '0',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE Archive (
ArchiveID int(5) unsigned NOT NULL auto_increment,
ArchiveDate varchar(30) NOT NULL,
validate int(1) NOT NULL default '0',
PRIMARY KEY (ArchiveID)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

Again hope this helps.

Arty

timmy0320

Arty, I have somewhat something figured out. My database is setup differently than yours. I wasn't using the code that you posted though. I got some help from somewhere else. I just now need to figure out another step for mine to display correctly then I can start formatting it to be able to display and collapse.

<?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);

$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['day'].' - '.$row['title'].'<br />';
    }
 }
?>

It will now display like so

2008 - 04 [2]
04 - Test
01 - Test post.
1993 - 04 [1]
13 - Test post.
1993 - 03 [1]
01 - Test.
1984 - 04 [1]
09 - test

I now have to get it to display in a tree with the year above the month which is currently displayed next to the year. Like:

2008 [2]
-April
--04 - Test
--01 - Test post.

GIJew

Hey Timmy,

To get it to display the tree I would think you should run similiar queries so the first one would get the distinct year and than you could loop through that and once inside of that loop query to get all posts for that year.

Just a suggestion.

timmy0320

I got it all figured out now. Just added a couple more loops into it and changed the query a bit like you did and added a couple more loops