27 November 2003 Archives

Recovered from the Wayback Machine.

At Burningbird, I modified my Movable Type template to display a small graphic associated with the subject (category) of a posting next to its title. Those who are less interested in my technology writing can then skip postings with the associated binary graphic next to the title; those who are uninterested in politics, can avoid that graphic, and so on. (My friend Chris at Empty Bottle also uses graphics to designate categories. However, his graphics are a lot more sophisticated than mine.)

I thought about creating multiple weblogs and focusing each on a different topic within the framework of my writing as ‘Burningbird’, but I wouldn’t write more (or less) on any subject just because I split them out into different weblogs. All I would do is scatter my thoughts about like dried bits of corn on a dusty field, forcing my readers to take on the visage of Crow, pecking about hoping to find that edible kernel among the dirt.

Besides, my thoughts don’t split cleanly along subject and topic, neatly categorized into discrete buckets. I’m just as likely to throw new photographs or a bit of writing whimsey into an essay on RDF, or mix a little technology into an essay on the Environment. My weblog reflects my writing, which reflects my mind: muddied waters of blended interest.

First, I created all the graphics of a relatively uniform size. I made them slightly longer than the heading caption bar, as I wanted to drop just below it. I then saved the graphics in the PNG format, naming them the exact name of the category.

Next, to add the graphic, within the main index template, I found the entry section associated with the posting title, as marked with the use of the MT template tag <$MTEntryTitle$>. I then replaced that tag with the following, which not only displays the graphic, but also has a link to the category page for people who want to read more entries based on that category:

<a href=”MTBlogArchiveURL<$MTEntryCategory dirifty=”1″ $>/index.htm”><img src=”http://weblog.burningbird.net/mm/<$MTEntryCategory$>.png” alt=”<$MTEntryCategory$>” align=”left” hspace=”6″ border=”0″ /></a>
<div class=”titlebox”><span class=”title”><a style=”text-decoration: none” href=”<$MTEntryLink$>”><$MTEntryTitle$></a></span></div>

The exact same template code can be used with the title on each individual page, for the same effect.

Graphic/Topic:

– Adventure

– Connecting

– Culture

– Environment

– Life

– Metablogging

– Neighborhood

Photography

– Politics

– Sensory

– Technology

Writing

– Sensuous Technology

– Women’s Writing

Recovered from the Wayback Machine.

My weblogs show a Recent Comment/Trackback list that I’ve implemented using SQL and PHP rather than MT tags. The main reason I didn’t use tags is that I filter comments to showing only those that are on posts 30 days old or newer. This helps focus comments on current conversations, and also helps cut down some of the comment spamming problems.

An additional requirement for my Last Comments/Trackbacks list to intermix the comments and trackbacks into one list, showing the most recent items regardless of type of comment — local or remote. There are plug-ins to use to do some of this, but I like to keep my fingers into the PHP/SQL world.

To manage this, what I did was use what is called a SQL union. A SQL union operates pretty much as it sounds: it creates one set of data that’s the union of the result of two separate queries, and this data is what’s returned to the PHP program for processing. Unions have been around in Oracle and Sybase and other databases for some time now, but only added to MySQL in version 4.x. Luckily most of us are using 4.x.

The query string I’m using in my PHP process is:

$sql = ‘( SELECT tbping_id, tbping_source_url, tbping_title, entry_title, entry_id, blog_archive_url, tbping_created_on, 1 \’flag\’, category_label FROM mt_entry, mt_tbping, mt_trackback, mt_blog, mt_placement, mt_category WHERE entry_id = trackback_entry_id AND trackback_id = tbping_tb_id and entry_blog_id = blog_id AND entry_status = 2 AND placement_entry_id = entry_id and placement_is_primary = 1 and category_id = placement_category_id ORDER BY tbping_created_on DESC LIMIT 20 ) UNION ( SELECT comment_id, comment_url, comment_author, entry_title, entry_id, blog_archive_url, comment_created_on, 2, category_label FROM mt_comment, mt_entry, mt_blog, mt_placement, mt_category WHERE entry_id = comment_entry_id AND entry_blog_id = blog_id AND entry_status = 2 AND placement_entry_id = entry_id and placement_is_primary = 1 and category_id = placement_category_id and TO_DAYS(NOW()) – TO_DAYS(entry_created_on) <= 30 ORDER BY comment_created_on DESC LIMIT 20 ) ORDER BY 7 DESC LIMIT 20 ‘;

As you can see, this query is not necessarily for the faint at heart, or someone who isn’t familiar with SQL. I could at this point just tell you to copy and past this into your own page. However, if you’re like me, you don’t necessarily like using technology without having a better understanding of exactly what it is you’re doing, and why.

In this multi-part roll-out of the MySQL/SQL for Poets weblog, I’m going to cover all the different components of this query, including providing a basic introduction to the SELECT statement, using functions with queries, and ending with the UNION and how this query is used within PHP to provide the recent comments/trackbacks list.