Archiving Blog entries..

[Patch^]

Hi All, I'm having ago at creating my own blog using PHP and mySQL and I wanted to know if anyone had an idea on how to auto archive entries by date month/year etc.. i.e. July 2007, August 2007, September 2007 and so on.

I came across this on the forum:

http://www.kirupa.com/forum/showthread.php?t=267120&highlight=php+archive+blog

I think it's similar to what I need to accomplish but I'm not sure how to go about it.

Any help would be great,

Thanks!! :)

[eirche]

just query "WHERE data >= june 1 AND date < july 1" for whole month of june. don't seem that difficult.

[Patch^]

just query "WHERE data >= june 1 AND date < july 1" for whole month of june. don't seem that difficult.

I see what you mean, but surely I would have to do that for each month? What about year too? i.e. 2007, 2008, 2009 and so on...

Anyway this tutorial seems to be helping a little :

http://www.interaktonline.com/Support/Articles/Details/Building+a+Blog+%233:+Creating+Search+and+Archive+ features-Linking+the+Calendar+Days+to+the+Articles.html?id_ art=18&id_asc=171

[eirche]

you want to move old entries to another table. is that really necessary? what do you have difficulty on? the logistics of comparing date, or using sql?

[Patch^]

Well I'm basically just trying to figure out how to get it so that it archives my blog entries, like in wordpress or blogger. So that all the entries/articles that I make in July go into a new category named July 2007 (or depending on the year July 2008 etc, this goes for all months by the way) then when I get to August 2007 in creates a new category and puts all the entries in "August 2007". Basically a standard blog, but without using wordpress or blogger....

I don't need the data to be transfered to a new table, just so that it recognizes the date and puts it into a new category for july 07, aug 07, sept 07, nov 07, dec 07, jan 08 and so on..

I've only been dabbling in PHP only for a short bit so I'm looking for any tutorials or advice that will help me.

I'm guessing that I'm going need a query that sees the month and year of the article and then puts it in a new category/creates the category.

In the blog I'm working on at the moment, I've got it so I can create entries, comments and categories...now the next stage is archives, then various other bits and bobs. Good process so far on learning PHP :D

Thanks :) ,

[eirche]

the entries need not to be categorized physically in the database. dynamically take the oldest date and now to make the list of months. e.g. oldest date = april 2005, you make all the months from that date to now.

[Patch^]

I know they dont need to be categorized in the database. I know it has to be done dynamically. But how to do it is what I'm asking. I also want the "categories" to show up when the month comes and stay there, not just have a long constant list, which sounds a little like what your saying. Maybe I'm wrong.

Here is an old blog I had ages ago look on the right where it says "Archives"

http://liamchapman.wordpress.com/

[Danii]

You should probably have a field in your table named date or something. I usually store unix timestamps in it cause theyre so easy to work with. Anyways, start with pulling out all posts from your database and have them sorted after id or date, doesnt really matter. Now you can archive these posts however you want since you have the date information.

Start your archiving using your oldest post, find out what date it was posted, let's say 22 july 2003. If you used unix timestamps you can get a timestamp for august 1 2003 using mktime. Once you have that timestamp ($new_date), pull out all posts which have a timestamp less than $new_date.

I hope you get what I'm trying to do, I'm in a hurry right now so feel free to pm me if you need more help. Good luck

[evildrummer]

Have a field for year, field for date.

Then query it:

SELECT * FROM table WHERE year=2007 && date=[insert whatever]

Can't you do it like that?

[foodpk]

evildrummer: having a seperate column for a date and a year is bad.

Store your dates either as a date type column or as an integer type column and then put the unix timestamp in.
So let's say you store the dates in unix timestamp format. If you want to get all the posts that were made on july 2007, you'd do something like


SELECT * FROM posts WHERE MONTH(FROM_UNIXTIME(posts.date)) = 7 AND YEAR(FROM_UNIXTIME(posts.date)) = 2007;

Now say you got the year and month from your url segments and stored them in variables, you'd do something like

$query = 'SELECT * FROM posts WHERE MONTH(FROM_UNIXTIME(posts.date)) = ' . $month . ' AND YEAR(FROM_UNIXTIME(posts.date)) = ' . $year;
provided that you've made sure that $month and $year are safe for query insertion.

[foodpk]

Also, "archiving" blog posts should be done on your presentation layer, not your data layer. That means that you shouldn't have separate tables for archived content or anything. Archives are just a fancy way of saying that the last 10 posts or whatever show up on the front page and then there's some sort of different mechanism for viewing posts older than that, like in wordpress with the month thing.
Although I must say, I've always found wordpress' way of doing the archives kind of lame, especially when you have blogs that post updates like twice a month. You have to click through a boatload of months and each month just displays two posts. I'd rather see all the post titles on one page, separated by months as headings maybe.

[Patch^]

Thanks guys, I'll try out what you suggested and let you know how I get on. thanks :)

[icio]

You don't really have to do anything for archiving. It's much more simple than you appear to think it is.

Basically what you're doing with an archive is two things:-
1. Generating a list of unique months on which there are posts
2. Getting all posts within a certain month.

The best way to do this is to have a `creation date` field in your database table so that you can work out when a post was created. When you are creating your new post (inserting into database, specifically), you need to give a value for the creation date. To do this in your SQL statement is very straight forward and is in-fact handled by SQL very well:

INSERT INTO `posts_tbl` (`field1`, `field2`, `creationDate`) VALUES ('value1', 'value2', NOW());

The next thing that you are likely to do is to show, to the user, a list of unique months on which there are posts. This is also quite straight-forward: we use the MySQL date function and GROUP BY to return the set of unique months.

SELECT MONTH(`creationDate`) as month, YEAR(`creationDate`) as year FROM `posts_tbl` GROUP BY MONTH(`creationDate`), YEAR(`creationDate`);
Then when looping through the data set in PHP you can do things like:

$result = mysql_query("{as above}");
while ($date = mysql_fetch_assoc($result)) {
echo "<a href=\"archive.php?m={$date['month']}&y={$date['year']}\">Month: {$date['month']}, Year: {$date['year']}</a>
}
Though you might want to put some different text on the links using the PHP date functions to get the month names.

So next, now that the user has followed one of your links to the archive page you're going to want to retrieve the posts of the requested month and year. This is also quite simple! Hurrah, and again uses the MySQL data functions but with WHERE now too.

SELECT * FROM `posts_tbl` WHERE MONTH(`creationDate`)=$month AND YEAR(`creationDate`)=$year;Coupled with PHP like

$month = intval($_GET['m']);
$year = intval($_GET['y']);
$result = mysql_query("{as above}");
// ...


Then that's all there really is to it. Hope that helps :thumb:

[Patch^]

wow thanks icio!! The method your suggesting sounds what I'm after at the moment, i'll give it ago in a bit and let you know how it goes :)

[eirche]

while it is elegance solution, generating a list months from database is a big performance hit.



// 32-bit unix timestamp
define('BLOG_BIRTH', strtotime('December 2004'));
// i always use this because time() may give different values
// through out script execution
define('NOW', time());

$time = BLOG_BIRTH;
while($time < NOW)
{
$date_str = date('F Y', $time);
$next_month = strtotime("next Month", $time);

printf('<a href="archive.php?from=%d&to=%d">%s</a><br>', $time, $next_month, $date_str);

$time = $next_month;
}


note: 32-bit unix timestamp is good for 1970 to 2037

[icio]

Nice post, eirche. The thing with your method is that it will also list months that contain no posts, which is why I opt for my method of generating the list from the database. Also, you'd be best replacing all of those `strtotime` calls with an application of `mktime`.

[foodpk]

Yeah, that really depends on how frequently he intends to post. But like I said, I find that method of going through the archives completely lame, because the month when something was posted really has very little correlation to the (potential) content, so clicking thorough them is unintuitive and a pain. I'd much rather see something like this:

The Archives
July 2007
One post title
Another post title
Third post title

June 2007
One post title
Another post title

May 2007
Hai guyz this is my first post

[Patch^]

I'd just like to thank everyone who has taken interest in my topic :) But I'm still not fully understanding on how to implement icio code.

Would it be something like this?




//Archive code///

$result = mysql_query("SELECT MONTH(`date`) as month, YEAR(`date`) as year FROM `cr_entry` GROUP BY MONTH(`date`), YEAR(`date`)";
while ($date = mysql_fetch_assoc($result)) {
echo "<a href=\"archive.php?m={$date['month']}&y={$date['year']}\">Month: {$date['month']}, Year: {$date['year']}</a>";
}

$month = intval($_GET['m']);
$year = intval($_GET['y']);
$result = mysql_query("SELECT * FROM `cr_entry` WHERE MONTH(`date`)=$month AND YEAR(`date`)=$year");



Then I create a php file named archive where I have a recordset/content for the entries etc...?

[Patch^]

Nevermind, think I got it working.. Thanks icio!!

[Patch^]

Works fine now, but in the archive bit is showing up as "7, 2007" where as I want it as "July, 2007". Now I know the formats for the date and stuff i.e. date( ymd) & date(YMd) and so on. I there a way to convert the number to the month quickly? Perhaps Im doing something wrong...again lol..

[icio]

Though you might want to put some different text on the links using the PHP date functions to get the month names.This is what I was talking about here.

What you need to do it something like the following:

$result = mysql_query("{as above}");
while ($date = mysql_fetch_assoc($result)) {
echo "<a href=\"archive.php?m={$date['month']}&y={$date['year']}\">".date("F, Y", mktime(0,0,0,intval($date['month']),1,intval($date['year'])))."</a>
}

PHP Date Functions (http://php.net/date)

I've not tested it, but I think it should do the trick. Hope that helps :thumb:

[Patch^]

Yay everything is working the way I want!!! Thank you icio and everyone else who contributed :)

[icio]

Awesome. Glad I could help. :thumb:

Can we see?