You are currently only viewing posts within the category: Site news
You are here: Home → Archive → Site news → 2004 → May → 19th → this post
19th May 2004, mid-afternoon | Comments (28)
About four times a week I get an email from someone keen to know how I coded my blog archive page — the headings and nested lists approach seems to be a decent way of presenting the information, and people wonder how the page is constructed. I usually reply to these emails saying that it’s a bit too complicated to explain, but today I’m relenting. I’ve posted some example code, below, and explained the basic concept behind it. It’s up to you guys to adapt it for your own purposes.
The thing to note about this method is that everything is done with a single database call. All the logic that determines what gets printed where is managed by PHP. I’ve commented the code as heavily as I can, and I’ll hope that as you put it into action you’ll understand exactly what’s going on. If you get a bit confused, don’t worry, I still get a the occasional brain-twinge when I look at this stuff.
Let’s start with a pretty simple set of data; we’ll take five posts, each assigned to a category, and we’ll display them using headings (for the categories) and unordered lists (for the posts).
<?php/* what we're going to do here is produce a set of unordered lists, each topped by an h3 heading of the category. The tricky bit is that we're going to do it with only one SQL call. The logic below does a lot of "If the category name of the current record is the same as the last one then don't print the category name heading" sort of thing.Given these results from our SQL query:---------------------------| Category | Post Title |---------------------------| Category 1 | Post 1 || Category 1 | Post 3 || Category 1 | Post 4 || Category 2 | Post 5 || Category 3 | Post 2 |---------------------------This is the sort of output we can expect:<h3>Category 1</h3><ul><li>Post 1</li><li>Post 3</li><li>Post 4</li></ul><h3>Category 2</h3><ul><li>Post 5</li></ul><h3>Category 3</h3><ul><li>Post 2</li></ul>*/// set query (you'll have to write this yourself, this is just an example)$query = "SELECT DISTINCT post_title, post_category FROM post ORDER BY post_category ASC, post_title ASC";// run query$result = @mysql_query($query);// if rows were returned from the queryif (($result) && (mysql_num_rows($result) > 0)){// variables explained:// $current_stored_category -- this will hold the name of the category of the previous post, this will be compared to the category of the current post in order to determine if the category has changed.//$is_very_first_list -- this will be used to determine if the post in question is the very, very first post in the whole data set, if it isn't then we'll use that knowledge to print tags that will close off the list that was printed before it.// set our variables to their initial values$current_stored_category = '';$is_very_first_list = 1;// loop and fetch all the resultswhile ($row = mysql_fetch_assoc($result)){$post_category = $row['post_category'];$post_title = $row['post_title'];// if this post has a different category to the one stored in $current_stored_categoryif ($current_stored_category <> $post_category){// set the stored category to equal this post's category$current_stored_category = $post_category;// because we're in a new category we need to close off the last category's <ul>. However, we must check that we're not at the very, very beginning of the whole thing, because if we are there won't be a previous list to close offif ($is_very_first_list == 0){print '<ul>'."\n";}// we must also print the new category as an <h3> heading and start our new <ul>print '<h2>'.$post_category.'</h2>'."\n\n";print '<ul>';}// now we print out the post itemprint '<li>'.$post_title.'</li>'."\n";// now we alter this variable value so we know we're not at the very, very start of the whole thing anymore$is_very_first_list = 0;}// now we print a closing <ul> to finish off the last recordprint '</ul>'."\n\n";}// if no rows were returned from the queryelse{// error message goes here}?>You can apply this technique to practically anything that involves grouping of results; and if you need to add in an extra level of grouping (as I do in my archive pages, where I group by Month and then by Day as well) it’s not too hard to insert that extra functionality.
Just to show that, here’s an extension of the previous example, with a few more posts, and a set of sub-categories added in.
<?php/* what we're going to do here is produce a set of nested lists, each topped by an h3 heading of the category. The tricky bit is that we're going to do it with only one SQL call. The logic below does a lot of "If the category name of the current record is the same as the last one then don't print the category name heading" sort of thing.Given these results from our SQL query:-------------------------------------------| Category | Sub-category | Post Title |-------------------------------------------| Category 1 | SubCat 1a | Post 1 || Category 1 | SubCat 1a | Post 3 || Category 1 | SubCat 1b | Post 6 || Category 1 | SubCat 1c | Post 4 || Category 2 | SubCat 2a | Post 5 || Category 2 | SubCat 2a | Post 8 || Category 2 | SubCat 2b | Post 7 || Category 3 | SubCat 3b | Post 2 || Category 3 | SubCat 3b | Post 9 |-------------------------------------------This is the sort of output we can expect:<h3>Category 1</h3><ul><li>SubCat 1a<ul><li>Post 1</li><li>Post 3</li></ul></li><li>SubCat 1b<ul><li>Post 6</li></ul></li><li>SubCat 1c<ul><li>Post 4</li></ul></li></ul><h3>Category 2</h3><ul><li>SubCat 2a<ul><li>Post 5</li><li>Post 8</li></ul></li><li>SubCat 2b<ul><li>Post 7</li></ul></li></ul><h3>Category 3</h3><ul><li>SubCat 3b<ul><li>Post 2</li><li>Post 9</li></ul></li></ul>*/// set query (you'll have to write this yourself, this is just an example)$query = "SELECT DISTINCT post_title, post_category, post_subcategory FROM post ORDER BY post_category ASC, post_subcategory ASC, post_title ASC";// run query$result = @mysql_query($query);// if rows were returned from the queryif (($result) && (mysql_num_rows($result) > 0)){// variables explained:// $current_stored_category -- this will hold the name of the category of the previous post, this will be compared to the category of the current post in order to determine if the category has changed.// $is_very_first_list -- this will be used to determine if the post in question is the very, very first post in the whole data set, if it isn't then we'll use that knowledge to print tags that will close off the list that was printed before it.// $current_stored_subcategory -- this will hold the name of the sub-category of the previous post, this will be compared to the sub-category of the current post in order to determine if the sub-category has changed.// $is_very_first_sublist -- this will be used to determine if the post in question is the very, very first post in the category data set, if it isn't then we'll use that knowledge to print tags that will close off the list that was printed before it.// set our variables to their initial values$current_stored_category = '';$is_very_first_list = 1;$current_stored_subcategory = '';$is_very_first_sublist = 1;// loop and fetch all the resultswhile ($row = mysql_fetch_assoc($result)){$post_category = $row['post_category'];$post_subcategory = $row['post_subcategory'];$post_title = $row['post_title'];// if this post has a different category to the one stored in $current_stored_categoryif ($current_stored_category <> $post_category){// set the stored category to equal this post's category$current_stored_category = $post_category;// reset the subcategory vars$current_stored_subcategory = '';$is_very_first_sublist = 1;// because we're in a new category we need to close off the last category's <ul>. However, we must check that we're not at the very, very beginning of the whole thing, because if we are, there won't be a previous list to close offif ($is_very_first_list == 0){print '</ul>'."\n";print '</li>'."\n";print '</ul>'."\n";}// we must also print the new category as an <h3> heading and start our new <ul>print '<h2>'.$post_category.'</h2>'."\n\n";print '<ul>'."\n";}// if this post does have the same category as the one stored in $current_stored_categoryelse{// alter this value so we know we're not at the start of a sublist$is_very_first_sublist = 0;}// if this post has a different subcategory to the one stored in $current_stored_subcategoryif ($current_stored_subcategory <> $post_subcategory){// set the stored subcategory to equal this post's subcategory$current_stored_subcategory = $post_subcategory;// because we're in a new subcategory we need to close off the last subcategory's <li>. However, we must check that we're not at the very, very beginning of the whole category, because if we are, there won't be a previous list to close offif ($is_very_first_sublist == 0){print '</ul>'."\n";print '</li>'."\n";}// now we print out the subcategory item and start off its new listprint '<li>'.$post_subcategory'."\n";print '<ul>'."\n";}// now we print out the post itemprint '<li>'.$post_title.'</li>'."\n";// now we alter this variable value so we know we're not at the start of the whole thing anymore$is_very_first_list = 0;$is_very_first_sublist = 1;}// now we print a closing <ul> to finish off the last recordprint '</ul>'."\n";print '</li>'."\n";print '</ul>'."\n\n";}// if no rows were returned from the queryelse{// error message goes here}?>And that’s about it. Just plug in your own database call and variable names and you should be good to go.
Jump up to the start of the post ↑
A collection of miscellaneous links that don't merit a main blog posting, but which are interesting none-the-less.
Our enemies are innovative and resourceful, and so are we. They never stop thinking about new ways to harm our country and our people, and neither do we.— George W Bush (9)
Stuff from the intersection of design, culture and technology.(3)
A selection of blogs I read on a regular basis.