Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

Rows and Columns


SQL Anywhere perspective on technology

header image

Creating a WordPress multi-blog Tag Cloud

April 20th, 2009 · 1 Comment

Now that the new blogs have been up for a while and everything seems stable, the Sybase blogging homepage is undergoing some changes. One of the things that was recently added is a tag cloud, which allows visitors to get a quick picture of the topics the Sybase bloggers are blogging about, and even click through to view the list of articles associated with a specific tag. There is no mandated tag set, so the tag cloud is quite large on the page in its full view, which is why there is a shortened view available by default which gives a sampling of the tags.

While the visual representation of the tag cloud was done by the Sybase Web Marketing team, the actual data comes from the blog content. Since I had already experimented and figured out how to generate RSS from the database, the only thing I really had to figure out was how to get the tag information out to the tag cloud.

I started by determining where the tag information was stored in the database:

SELECT name AS tag, "count";
FROM terms t, term_taxonomy tt
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;

This gave me the tag list for a single user. Since I created each blogger with their own set of tables in the same SQL Anywhere database, I simply had to union this query together once for each blogger, changing the table names each time. At some point in the future, I may make this more dynamic so it automatically picks up new bloggers, but for now it works fine:

SELECT name AS tag, COUNT 
FROM wp_terms t, wp_term_taxonomy tt
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
UNION ALL
SELECT name AS tag, COUNT 
FROM wpuser1_terms t, wpuser1_term_taxonomy tt 
WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
...

As I said above, there is no standard tag list for the bloggers. This meant there was a risk of different bloggers using the same tag with different case and the tag would show up multiple times. In addition, I wanted to eliminate any tag that was only used once in total across all blogs, and return the combined count for each individual tag. I did this using a group by and having; clause. My final query looked something like this:

SELECT LOWER(tag) AS tag, SUM(COUNT) AS COUNT 
FROM (SELECT name AS tag, COUNT 
	FROM wp_terms t, wp_term_taxonomy tt
	WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
      UNION ALL
      SELECT name AS tag, COUNT 
	FROM wpuser1_terms t, wpuser1_term_taxonomy tt 
	WHERE t.term_id = tt.term_id AND taxonomy = 'post_tag' AND "count" > 0;
      UNION ALL
      ...
     ) AS T1
GROUP BY tag 
HAVING COUNT >  1 
ORDER BY SORTKEY(tag, 'nocase');

Notice the result set is sorted by tag. This made it easier to classify the tags into various categories for display purposes.

Now that I had the data, I had to put it into a format that made it easy/useful for the Sybase Web Team to display. I’ll talk about that in my next post.

Be Sociable, Share!

Tags: SQL Anywhere · WordPress

1 response so far ↓

  • 1 Formatting a Tag Cloud // Apr 27, 2009 at 9:19 am

    [...] RSS ← Creating a WordPress multi-blog Tag Cloud [...]