retrieving tags and counts from WordPress blog

A website I’ve been working on has a WordPress blog included in it. The site is not using WordPress as the CMS, just as one section. While the blog is sub-foldered (e.g. http://[SITENAME]/blog/) there was still a need to refer to the tags elsewhere on the website, and link to them. While it would definitely be possible to reference components of the blog itself, and then call the function/s to get tags and counts etc, I decided to hook into the DB with some SQL and rock it from there.

After a quick explore of the WordPress DB, I pulled the tables and fields I needed to reference in order to get the results I desired. The query itself is pretty straight-forward:

SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name 
FROM wp_term_taxonomy, wp_terms 
WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id 
AND wp_term_taxonomy.taxonomy =  'post_tag';

Walking through this query step-by-step we have:

1 – SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name

choose the values we want, and from which tables, and give them an alias

2 – FROM wp_term_taxonomy, wp_terms

define the tables we are referencing during this query

3 – WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
AND wp_term_taxonomy.taxonomy = 'post_tag';

put conditions to the results; here we want to make sure we are referring to the same term, and that it is a term of type ‘post_tag’

Implementation is very simple, the sample below is a delivery in PHP:

// connect to your DB
$sql = "SELECT wp_term_taxonomy.count AS count, wp_terms.name AS name ".
"FROM wp_term_taxonomy, wp_terms ".
"WHERE wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id ".
"AND wp_term_taxonomy.taxonomy =  'post_tag';";
$taglinks = "";
$result = @mysql_query($sql);
if($result) {
	while($row = mysql_fetch_array($result)) {
		$tag = $row['name'];
		$count = $row['count'];
		$taglinks .= "<a href='http://[SITENAME]/blog/?tag={$tag}' 
				class='tag-link-{$count}' title='{$tag}' >
				{$tag}</a><br /> ";
	}
	// close the DB connection for security, e.g.
	// mysql_close([CONNECTION]);
}

In my example, I have retrieved the tags and their counts and formatted them into absolute links which are styled based on the number of uses. This enables them to be displayed more prominently based on popularity for example.

WordPress iPad app

So, I’ve been playing with my iPad WordPress app, and it keeps dropping my password. I go into settings and reset, and it works for a day or so, then FAIL!!

If I had to log in every time through the app it wouldn’t be so much of a problem but, as it stands, I have to exit the app and navigate to Settings, re-enter the password and then navigate back to the app to get anything done.

Just sayin’

checking out the new tweet-embed plugin for WordPress

Just to try out this new tweet-embed plugin for WordPress, I’ll post the tweet of my this blog post just to be self-referential [this meant I edited the post after completion and it being automatically tweeted].

http://twitter.com/#!/cttwtr/status/1126307979071488

It pulls the tweet from Twitter with all the same styling as the Tweeter’s own Twitter page, but this also means it keeps the tweet live for each visitor to your blog (ie the time stamp displayed is relative to your page viewer’s time of visit to your blog).