As I mentioned in my latest post 'Microblogging with Twitter' we have added tweets to this blog and the two latest are always displayed on the right-hand side of the site, for me and Randolph.



If you look more closely you might notice that there is a new block below these twitter blocks called 'Guest Tweets by...'. This is a new functionality we thought might be fun and interesting. Anyone with a Twitter account can send us a mail with there Twitter nickname and we will add it to the database. Each time the site is reloaded two tweets will be displayed for a random user stored in the database.

The only thing we really need is your Twitter nickname, but you would save me some research if you could hand me some more information as well. I know, I could build some cool functions that looks up what I want based on a nickname but I will have to do that some other day. Let me know if you have such that I can use.

What we need is specified in the prepared mail that will open in your preferred mail client if you click on the mail icon next to "Do you want guest tweets on Wohill". (or this one)

Please join us, we might think of other fun stuff to do later on since Twitter has an interesting and evolving API and lots of talented users are doing cool stuff with it.

To accomplish this guest twitter functionlity I used the functionality specified in the earlier post and added a new function which gets data from the database (a random row), creates an array and returns it. This function is called from the same page as the div blocks are located. The id-specific values of the 'getTwitters'-function and the div-block are populated with the values from the returned array of the function below. Everytime Wohill is reloaded a new random users tweets will be displayed.

Here is the function (php):

function getRandomTwitterUserInfo() { /* Local login variables */ $mysql_username = 'username'; $mysql_password = 'password'; $mysql_host = 'host'; $mysql_database = 'db'; $twitter_table = "twitterUserInfo"; /* Create a connection and set current active database */ $link = mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Could not connect to database: ' . mysql_error()); mysql_select_db($mysql_database, $link) or die('Could not set database'); $select_query = sprintf("SELECT * FROM %s", $twitter_table); $select_result = mysql_query($select_query); /* Check result */ if (!$select_result) { $message = 'Invalid query: ' . mysql_error() . '\n'; $message .= 'Whole query: ' . $select_query; die($message); } $random_row = rand(1, mysql_num_rows($select_result)); mysql_data_seek($select_result, $random_row); $row = mysql_fetch_assoc($select_result); $twitterUserInfo = array($row['twitterId'], $row['twitterNickname'], $row['twitterImageUrl']); return $twitterUserInfo; }

This is the second article about tags.

Our tag cloud is promoted on the bottom left of this blog with this picture. Here is a short guide on how to create a tag cloud on your site using PHP and MySQL.



It is assumed that tags are stored in the database as a string separated by '#' and with a leading and trailing '#'. The purpose of this comes to use later on when creating links for related blog entries.

When the tag cloud is built up all tags are fetched from the database and put in an array.
A new array is populated with unique values pointing to the number of total occurences of a key. See code below:

/* Get all tags from all published posts and put them in an array as lower cased and trimmed. Count all occurences of the elements and create a new array which is an array of keys of unique elements pointing to values indicating how many of them there are in the source array. Return the array. */ function getAllTags() { $mysql_table = 'the_table'; /* An empty tag has the default value '__default__'. A non-empty tag has the format '#tag1#tag2#tag3#' and so on. */ $select_design_query = sprintf("SELECT * FROM %s WHERE (tags <> '__default__' AND published = 'yes')", $mysql_table); $select_design_result = mysql_query($select_design_query); if (!$select_design_result) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $select_design_query; die($message); } $subTags = array(); $allTags = array(); $counter = 0; /* Create an array containing all tags */ while ($row = mysql_fetch_assoc($select_design_result)) { /* Remove first and last character from string */ $subTags = substr($row['tags'], 1, -1); /* Create an array from the string */ $subTags = split("#", $subTags); /* Loop through array and put each value to lower case and trim */ foreach ($subTags as $value) { $allTags[$counter] = strtolower(trim($value)); $counter = $counter + 1; } } /* Create a new unique key->value array */ $tags = array_count_values($allTags); return $tags; }

Print the values of the array after each other as links and with different font sizes depending on the number of total occurences for a specific tag. This function has been taken from byteMyCode with a minor modification of the link of each tag. Each link is of the type <http://www.wohill.com/tags/some_tag>. This url is actually a rewrite url specified in htaccess. See this former entry about ht-access for more information. Make a call to this function where you want the tag cloud to be located.

function printTagCloud($tags) { arsort($tags); $max_size = 32; // max font size in pixels $min_size = 10; // min font size in pixels // largest and smallest array values $max_qty = max(array_values($tags)); $min_qty = min(array_values($tags)); // find the range of values $spread = $max_qty - $min_qty; if ($spread == 0) { // we don't want to divide by zero $spread = 1; } // set the font-size increment $step = ($max_size - $min_size) / ($spread); // loop through the tag array foreach ($tags as $key => $value) { // calculate font-size // find the $value in excess of $min_qty // multiply by the font-size increment ($size) // and add the $min_size set above $size = round($min_size + (($value - $min_qty) * $step)); echo '<a href="http://www.wohill.com/tags/' . $key . '" style="font-size: ' . $size . 'px" title="' . $value . ' things tagged with ' . $key . '">' . $key . '</a> '; } }

More about how to create related blog entries soon.

This is the third article about tags.

Once you have started tagging your entries this information can be used in several contexts and one of my favourites is the related blog entries functionality.

For each blog entry that is loaded to the user - tags for that entry needs to be matched to other tags in the database. All of these other tags are being used in the same way as a marker or keyword for one or several other entries. So other entries that have one or several of those matching tags will be displayed as related blog entries. This is a very dynamic and nice solution that updates both old and new entries with the most recent information automatically.

So here are again a couple of php-functions to solve this. As in the former example it is assumed that the tags are stored as a #-separated string with a leading and trailing # in the database for each entry.

Make these two calls in the place where you want to display related entries:

$resultSet = returnMatchingDesignsByTagsAsResult($row['tags'], "#"); printRelatedEntries($resultSet, $row['id']);

Where $row['tags'] is the tags-string.

The function 'returnMatchingDesignsByTagsAsResult' performs a query to the database for entries with matching tags and returns a resultset with those entries. Here is where it comes to use with the leading and trailing # in the tags-string:

/* Return query result that contains design rows with matching tags */ function returnMatchingDesignsByTagsAsResult($tagsString, $delimiter) { $mysql_table = 'the_table'; $counter = 0; $tags = array(); /* Remove # characters at the beginning and at the end */ $subTags = substr($tagsString, 1, -1); /* Split string into array */ $subTags = split($delimiter, $subTags); /* Loop through array */ foreach ($subTags as $value) { $tags[$counter] = strtolower(trim($value)); $counter = $counter + 1; } /* Create beginning of SQL-string */ $queryString = sprintf("SELECT * FROM %s WHERE ((published = 'yes') AND (", $mysql_table); $counter = 0; /* Loop through array of tags */ foreach ($tags as $value) { $tmpStr = "%" . $delimiter . $value . $delimiter . "%"; /* Find all entries that have a column value 'tags' which includes a substring with the pattern #tag# */ /* If first element in array */ if ($counter == 0) { $queryString .= sprintf("(tags LIKE '%s')", $tmpStr); } else { $queryString .= sprintf(" OR (tags LIKE '%s')", $tmpStr); } $counter = $counter + 1; } /* Complete the SQL-string */ $queryString .= ")) ORDER BY publishDate DESC"; $result_set = mysql_query($queryString); if (!$result_set) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $queryString; die($message); } return $result_set; }

Function 'printrelatedEntries' uses the result set from the other function and the id of the current design to print related entries to the web page:

function printRelatedEntries($resultSet, $id) { /* If there is at least on hit */ if (mysql_num_rows($resultSet) > 1) { Print "<tr valign=\"middle\"><td></td><td>"; Print "<b>Related blog entries:</b> <br />"; while ($row2 = mysql_fetch_assoc($resultSet)) { /* Don't print the entry if it is the same as the current since this is not excluded from the result set */ if ($row2['id'] != $id) { Print sprintf("- <a href=\"http://www.wohill.com/design/%s/%s.html\" title=\"%s\">%s</a><br />", $row2['id'], createUrlTitle($row2['name']), $row2['name'], $row2['name']); } } Print "</td><td></td><td></td></tr>"; } }

You still have to create your own sql-table and fill it with data to query of course, but hopefully this will help someone on the way for their specific solution.