Tutorials

Part 5: Building an archives page

In Part 5 we’re going to build an archives page that sorts entries by year. We’ll also create a block for your sidebar that links to each individual year of posts.

Once you grasp the concepts here, you should be able to create archive pages that list by month (by changing the date format that we pull from the timestamp), by category (if you have categories, simply change the sql query to pull the category instead of the year), or any other factor.

Before we begin, let me explain how the url is going to work. Suppose you name your page archives.php. Now say you want to see a list of all entries from the year 2004. You’ll go to this url:

http://yourdomain.com/archives.php?year=2004

This is important, because this url defines the $year variable, which we are going to use in our sql query.

First things first, open php:

<?php

And connect to your database (remember to change the values to your own):

mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

Let’s then get the year from the URL:

if (!isset($_GET['year'])) {
    die("Invalid year specified.");
}
else {
    $year = (int)$_GET['year'];
}

Since we are going to sort by year, and we don’t have a year column (just a timestamp), we need to learn an important new skill in MySQL — pulling the year out of the timestamp INSIDE the sql query. So let’s put our sql query together carefully, understanding each part of it.

First we’re going to tell it which columns to select from the database. We only need the timestamp, id and title:

$result = mysql_query("SELECT timestamp, id, title

From our table, which is called php_blog:

$result = mysql_query("SELECT timestamp, id, title FROM php_blog

Now we want to tell it to only select those entries where the year equals our $year variable. But, wait, you say. We don’t have a year column. How can we do that? With MySQL’s FROM_UNIXTIME(). This allows us to format a unix timestamp in MySQL. We’re going to use it to format the timestamp in our timestamp column. Since we only want the year, we’ll format it as %Y (the MySQL code for the year). It’ll look like this:

FROM_UNIXTIME(timestamp, '%Y')

So, we’ll stick that in our query, like so:

$result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE FROM_UNIXTIME(timestamp, '%Y') = '$year'

Now tell it to order by id, descending:

$result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE FROM_UNIXTIME(timestamp, '%Y') = '$year' ORDER BY id DESC");

There you have it, there’s our query to get the timestamp, id, and title from all entries written in the year defined by the $year variable. Now let’s tell it what to do with them:

while ($row = mysql_fetch_array($result)) {

}

First we want to convert our timestamp to a date. Remember, you can format your date however you like, but this is the way mine looks:

while ($row = mysql_fetch_array($result)) {
    $date = date("l F d Y", $row['timestamp']);
}

Let’s format our title and id as variables (remembering to strip any slashes from the title):

while ($row = mysql_fetch_array($result)) {
    $date = date("l F d Y", $row['timestamp']);
    $id = $row['id'];
    $title = stripslashes($row['title']);
}

And format the listing. I have mine give the date, then a linebreak, the title as a link to the entry, and two more linebreaks (to separate the listings):

while ($row = mysql_fetch_array($result)) {
    $date = date("l F d Y", $row['timestamp']);
    $id = $row['id'];
    $title = stripslashes($row['title']);

    ?>

    <p><?php echo $date; ?><br /><a href="journal.php?id=<?php echo $id; ?>">&lt?php echo $title; ?></a></p>
    <?php
}

Close php:

?>

Let’s look at the whole archive page, start to finish:

<?php
mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

if (!isset($_GET['year'])) {
    die("Invalid year specified.");
}
else {
    $year = (int)$_GET['year'];
}

$result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE FROM_UNIXTIME(timestamp, '%Y') = '$year' ORDER BY id DESC");

while ($row = mysql_fetch_array($result)) {
    $date = date("l F d Y", $row['timestamp']);
    $id = $row['id'];
    $title = stripslashes($row['title']);

    ?>

    <p><?php echo $date; ?><br /><a href="journal.php?id=<?php echo $id; ?>"><?php echo $title; ?></a></p>
    <?php
}

?>

Now, suppose you want a little block in your sidebar that lists all of the years with a link to the appropriate archives page and the number of entries for that year. Open PHP:

<?php

Connect to your database (remember to change the values to your own):

mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

Now, this time we’ll use FROM_UNIXTIME() in the select to select just the years:

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y')

Now, we can actually tell it to call the results of that by a name of our choosing. Let’s call it get_year:

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year

We also want it to select a count (which is the way MySQL counts the number of entries):

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*)

And we’ll call that entries:

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*) AS entries

From our php_blog table:

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*) AS entries FROM php_blog

Now we’ll tell it to group the results by get_year. This will effectively get it to output each year just once:

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*) AS entries FROM php_blog GROUP BY get_year");

Now let’s tell it what to do with what we’ve selected:

while ($row = mysql_fetch_array($result)) {

}

Let’s define get_year and entries as variables:

while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];
}

Now we’ll have it echo our variables as links to the proper pages:

while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];

    echo "<a href=\"archives.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")<br />";
}

Close php:

?>

And our whole block looks like this:

<?php
mysql_connect ('localhost', 'db_username', 'db_password') ;
mysql_select_db ('db_name');

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT(*) AS entries FROM php_blog GROUP BY get_year");

while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];

    echo "<a href=\"archives.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")<br />";
}

?>

There you go!

Comments

Error Comments are closed for this entry.