World of Warcraft Tooltips Redux: phpBB 2 and Wowhead

My World of Warcraft guild's website has a phpBB 2 forum. It's not my favorite forum software due to lack of threaded comments, but we stick with it. Any WoW website worth its salt regularly mentions item names, at the same time offering a tooltip similar to the one you would see in-game and linking to one of the online item databases. I've blogged about this before, but the technology has come a long way since then. Now it's dead simple to get live AJAX tooltips with very little work: just include the Wowhead script in your <head> tag and you're done. However, there is still value in writing your own custom code.

I wanted to provide the following features:

  1. Link to an item using just the item name, no URL required.
  2. Automatically set the link color to match the item quality. (uncommon, rare, epic, etc.)

This is pretty easy to accomplish. I wrote the following code to cache item IDs and item quality, translating item names to Wowhead links with minimal network activity.

Create MySQL Table

First, create a new table to hold the item cache. I'll call it wow_wowhead to fit my existing table naming scheme. If you choose a different name, make sure you update the $table variable in wowhead_bbcode() below.

CREATE TABLE IF NOT EXISTS `wow_wowhead` (
  `id` int(10) unsigned NOT NULL default '0',
  `name` varchar(150) NOT NULL default '',
  `quality` tinyint(3) unsigned NOT NULL default '0',
  `updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `db` varchar(10) NOT NULL default 'www',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Modify phpBB

Append this code to the end of the $bbcode\_tpl assignments in phpBB/includes/bbcode.php, prepare\_bbcode\_template():

$bbcode_tpl['wowhead'] = '\'' . $bbcode_tpl['wowhead'] . '\'';
$bbcode_tpl['wowhead'] = str_replace('{A_ATTR}', "'. wowhead_bbcode('\\1') .'", $bbcode_tpl['wowhead']);
$bbcode_tpl['wowhead'] = str_replace('{NAME}', "'. str_replace('\\\"', '\"', '\\1') .'", $bbcode_tpl['wowhead']);

Add this to the end of the $patterns[] and $replacements[] section in phpBB/includes/bbcode.php, bbencode\_second\_pass():

$patterns[] = "#\[wowhead\](.*?)\[/wowhead\]#ise";
$replacements[] = $bbcode_tpl['wowhead'];

And add this function to the end of phpBB/includes/bbcode.php, being sure to include it before PHP's closing tag, the ?>:

function wowhead_bbcode($name, $whdb = 'www')
{
    global $db;
    $name = html_entity_decode($name);
    $table = 'wow_wowhead';

    $sql = sprintf("SELECT * from $table WHERE name = '%s' AND db = '%s'", mysql_escape_string($name), $whdb);
    $result = $db->sql_query($sql);
    $item = $db->sql_fetchrow();

    if(!$item)
    {
        $item = array('name' => $name);

        $url = 'http://' . $whdb . '.wowhead.com/?item=' . urlencode($name) . '&xml';
        $c = curl_init($url);
        curl_setopt($c, CURLOPT_RETURNTRANSFER, true);

        $xml_string = curl_exec($c);
        $xml = simplexml_load_string($xml_string);
        curl_close($c);

        list($quality) = $xml->xpath('/wowhead/item/quality');
        $item['quality'] = (int)$quality->attributes()->id;

        list($id) = $xml->xpath('/wowhead/item');
        $item['id'] = (int)$id->attributes()->id;

        $sql = sprintf("
            INSERT INTO $table (id, name, quality, updated, db)
            VALUES (%d, '%s', %d, NOW(), '%s')",
            $item['id'], mysql_escape_string($item['name']), $item['quality'], $whdb);
        $db->sql_query($sql);
    }

    return sprintf('href="http://%s.wowhead.com/?item=%d" class="q%d"', $whdb, $item['id'], $item['quality']);
}

Add the following to the bottom of your template's bbcode.tpl:

<!-- BEGIN wowhead --><a {A_ATTR}>[{NAME}]</a><!-- END wowhead -->

Add the following to your template's overall\_footer.tpl just before the </body\> closing tag:

<script src="http://www.wowhead.com/widgets/power.js"></script>

(If the flash of unstyled links bothers you, instead add it to overall_header.tpl just before the closing </head> tag.)

And that's it. As a bonus, the wowhead\_bbcode() function can be modified to work with other database objects or the vanilla PHP calls, if you need smarter tooltips for Mediawiki, WordPress, or any other PHP software.