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.
