I thought I should start making a habit of posting tutorials on the site. The web is full of great websites where people have taken the time to teach others what they have learnt and I feel it''s always good to 'pay it forward'.
My first tutorial will show you how to display news articles (with a recorded timestamp) stored in your MySQL database in reverse chronological order, ordering them by year and then month (newest -> oldest). So the end result should look very similar to the one on this website:
2012
January
• Happy new year!
2011
December
• Merry christmas
November
• Guy Fawkes night
2010
April
• April fools day!
I'll assume here that one of the columns in your MySQL table (we'll name it news) contains the timestamp recorded at the time the news article was created, for the sake of simplicity we'll call this column timestamp. Another assumption I'm making is that you have established a connection to your MySQL database in your script.
We begin by making a query to grab the timestamp of the newest news article in the database:
$get_newest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp DESC LIMIT 1") or die (mysql_error());If you have an id column in your database which is a primary key and is automatically incrememnted, you may choose to change the above query to ORDER BY id DESC LIMIT 1, it doesn't matter which you choose as they will both grab the newest article.
Next we want to check if in fact the database has any news articles yet, useful for any new websites being set up:
$rows = mysql_num_rows($get_newest);
if ($rows == 0) {
echo 'There are no news articles in the archive';
}
If the query returns zero rows then the database is empty. If it isn't empty we want to begin retrieving news articles and ordering them in the way that we want.
Similarly we grab the timestamp of the oldest news article:
$get_newest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp DESC LIMIT 1") or die (mysql_error());
$rows = mysql_num_rows($get_newest);
if ($rows == 0) {
echo 'There are no news articles in the archive';
} else {
$get_oldest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp ASC LIMIT 1") or die (mysql_error());
$data_oldest = mysql_fetch_array($get_oldest);
$data_newest = mysql_fetch_array($get_newest);
}We extract the information from both the newest and oldest queries store it in two separate arrays $data_newest, $data_oldest.
Next we get the year of both the oldest and newest dates and create a temporary variable $temp_year that we'll use to scan through from the newest year to the oldest:
$get_newest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp DESC LIMIT 1") or die (mysql_error());
$rows = mysql_num_rows($get_newest);
if ($rows == 0) {
echo 'There are no news articles in the archive';
} else {
$get_oldest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp ASC LIMIT 1") or die (mysql_error());
$data_oldest = mysql_fetch_array($get_oldest);
$data_newest = mysql_fetch_array($get_newest);
$newest_year = date("Y",$data_newest[timestamp]);
$oldest_year = date("Y",$data_oldest[timestamp]);
$temp_year = $newest_year;
}We've used the timestamp of the news article in the date() function to retrieve the year. The temporary year $temp_year has been set to the newest year as the order we require is newest -> oldest.
Now we want to loop through all years (between and including the newest and oldest years):
$get_newest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp DESC LIMIT 1") or die (mysql_error());
$rows = mysql_num_rows($get_newest);
if ($rows == 0) {
echo 'There are no news articles in the archive';
} else {
$get_oldest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp ASC LIMIT 1") or die (mysql_error());
$data_oldest = mysql_fetch_array($get_oldest);
$data_newest = mysql_fetch_array($get_newest);
$newest_year = date("Y",$data_newest[timestamp]);
$oldest_year = date("Y",$data_oldest[timestamp]);
$temp_year = $newest_year;
for($temp_year; $temp_year >= $oldest_year; $temp_year--) {
echo "<b>$temp_year</b><br />";
}
}Next we need to insert another loop that scans through each month of each year and grab the news articles belonging to that period and display them:
$get_newest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp DESC LIMIT 1") or die (mysql_error());
$rows = mysql_num_rows($get_newest);
if ($rows == 0) {
echo 'There are no news articles in the archive';
} else {
$get_oldest = mysql_query("SELECT timestamp FROM news ORDER BY timestamp ASC LIMIT 1") or die (mysql_error());
$data_oldest = mysql_fetch_array($get_oldest);
$data_newest = mysql_fetch_array($get_newest);
$newest_year = date("Y",$data_newest[timestamp]);
$oldest_year = date("Y",$data_oldest[timestamp]);
$temp_year = $newest_year;
for($temp_year; $temp_year >= $oldest_year; $temp_year--) {
echo "<b>$temp_year</b><br />";
for($month=12; $month >= 1; $month--) {
$check = mysql_query("SELECT * FROM news WHERE MONTH(FROM_UNIXTIME(timestamp))='$month'
AND YEAR(FROM_UNIXTIME(timestamp))='$temp_year' ORDER BY timestamp DESC") or die (mysql_error());
$check_rows = mysql_num_rows($check);
if ($check_rows == 0)
continue;
echo "<br /><i>".date("F", mktime(0, 0, 0, $month))."</i>";
while($data = mysql_fetch_array($check)) {
echo '<br />• <a href="yourwebsite.com/posts/read/id-'.$data[timestamp].'">'.$data[title].'</a>';
}
}
}
}The for loop begins with December (12), through to January (1) as specified by the condition $month >= 1. After each iteration, 1 is subtracted from $month ($month--) and the process continues. Inside each loop a query is executed to check if there are any news articles associated with that month and year, and if there isn't $check_rows will return zero and the command continue; is executed which skips the rest of the current iteration, deducts 1 from the month and checks again for any news articles for the next month/year. There's something very convenient going on in the $check query; the combination of MONTH() or YEAR() with FROM_UNIXTIME(), which are all MySQL functions, allows us to set a criteria for the year and month of the news articles we want. If there are any news articles for that month/year then we first print the name of that month using date("F", mktime(0, 0, 0, $month)) which takes the month in digit-form and returns the month name (string).
Finally we loop through each row returned by the query $check and store it's information in an array $data. Inside the loop we print out the information we want to display; in this tutorial I've chosen to display only the title of the news article (and a hyperlink to that article) under each month/year.
Chronological order
Some may want to show their articles in chronological order (oldest -> newest) and this only requires tweaks in two locations in the code that is the two `for` loops. For the first loop, rather than starting with newest year, we start with the oldest:
$temp_year = $oldest_year;
for($temp_year; $temp_year <= $newest_year; $temp_year++) { ... }
Furthermore we change the condition so that iterations continue for when the year is less than or equal to the newest year in the archive. Additionally $temp_year is incremented to proceed to the next year up. Note: you could very easily omit the first line and just have: for($temp_year=$oldest_year; $temp_year <= $newest_year; $temp_year++) { ... }, but I've broken it down for clarity.
For the loop concerning the month the adjustments are very similar; we begin with January (1) and iterate through to December (12) and increase $month by 1 after each iteration:
for($month=1; $month <= 12; $month++) { ... }That ends the tutorial and hopefully I haven't made any errors. I feel this method is transparent and very easy to grasp but of course this is one of many ways you can produce this kind of script.
Some may argue that for a database with a large amount of data, executing a query repeatedly in a for loop will increase overall page load-time. However I don't think is too much of an issue as I've not experienced any problems and very rarely does one list their entire archive on a single page (if very large) without adding some sort of pagination to control it.
If you have any questions/suggestions feel free to leave a comment.
| January 8, 2012 |