vBulletin Similar Threads in PHP

Posted 2009-04-18 in Scripts

This is a completely portable PHP script that fetches similar threads from a vBulletin forum. It does not require global.php or any other vBulletin scripts to run. The script uses full score matching, and supports search engine friendly URLs made by vBSEO and other custom formats.

A little background first. I could have used the built-in vBulletin functions, specifically fetch_similar_threads(), but that means invoking the vBulletin codebase with global.php from my scripts. The overhead of this just doesn’t seem necessary to grab a few threads, but I did not want to settle for a simple wildcard search and preferred score matching with the vBulletin fetch_similar_threads() function. In order to support custom search engine friendly URLs from vBSEO you need to include additional code to use the vbseo_any_url() function.

Not to mention, including vBulletin’s global.php isn’t as simple as it sounds. Errors are numerous under certain circumstances, and it simply doesn’t work under others. And if you want to fetch from a different server altogether… well you’re out of luck. Since I plan on invoking this script on every page load from a separate domain, I needed a better solution. Something portable and easy to customize.

With this in mind, I created a function vb_get_similar_threads(). This adapts the fetch_similar_threads() function in vBulletin and vbseo_any_url() function from vBSEO to an independent function that can be put in any PHP script with no extra include()’s or overhead.

Basically, you pass vb_get_similar_threads() a keyphrase string and the function connects to the vBulletin database and performs a full score matching search, returning all the matching threads as an array with formatted URLs.

Full instructions and usage examples are provided in the source code below. Feel free to use this for any purposes, but leave the copyright notice intact if you release any changes. There is no warranty on the code whatsoever. Save the source below to functions_vb_similarthreads.php on your server and modify it as needed.

functions_vb_similarthreads.php:

<?php
/**
 * functions_vb_similarthreads.php - Fetch related threads from a vBulletin forum
 *
 * This file contains portable functions for fetching vBulletin threads
 * w/ score-based phrase matching. It does not require any vBulletin files to
 * work as it connects directly to the vBulletin database.
 *
 * You can use this code for any purpose but keep this notice & copyright intact.
 *
 * Disclaimer: This code offers no warranty of any kind. Use at your own risk!
 *
 * @author      Gabriel Harper (http://www.gabrielharper.com/)
 * @copyright   (c) 2011 Scriptalicious
 * @version     1.0
 *
 * INSTRUCTIONS
 *
 * The main functionality is provided by vb_get_similar_threads(). This function
 * accepts a string to match against, a settings array containing database
 * details, thread URL format, and an optional list of forum IDs to exclude.
 *
 * You must define the thread URL format for your forum in your settings. This
 * URL format can contain these special variables:
 *
 * {thread.threadid}    - The unique thread ID
 * {thread.title}       - URL friendly thread title
 * {forum.title}        - URL friendly forum title
 *
 * The standard vBulletin URL format is:
 *
 * http://myforum.com/showthread.php?t={thread.threadid}
 *
 * vBSEO and custom URL formats can be formed, for example:
 *
 * http://myforum.com/{forum.title}/{thread.threadid}-{thread.title}.html
 *
 * vb_get_similar_threads() returns an array of thread details sorted by ID.
 *
 * USAGE EXAMPLE
 *
 * <?php
 * // Include this  file
 * include('functions_vb_similarthreads.php');
 *
 * // Define settings for the vBulletin forum
 * $settings = array(
 *      'hostname' => 'localhost',
 *      'name' => 'dbname',
 *      'username' => 'username',
 *      'password' => 'password',
 *      'threadurl' => 'http://myforum.com/showthread.php?t={thread.threadid}',
 *      'forumsbl' => '24,14,15,16,17'
 * );
 *
 * // Fetch 10 threads matching some phrase
 * $threads = vb_get_similar_threads('Gadgets and gizmos', $settings, 10);
 *
 * // Loop through threads and print out links
 *  foreach($threads as $threadid=>$thread) {
 *      echo '<a href="' . $thread['url'] . '" title="' . $thread['title'] . '">' . $thread['title'] . '</a>';
 *  }
 * ?>

 *
*/

/**
 * function vb_get_similar_threads(string $text, mixed $foruminfo, int $limit)
 *
 * Connects to the vBulletin DB, finds matching threads, and returns them all.
 *
 * @param $text         string      // Any string of text to match against
 * @param $foruminfo    array       // Database info & settings
 * @param $limit        int         // How many threads to return
 *
*/
function vb_get_similar_threads($text, $foruminfo, $limit = 5)
{
    $text = addslashes($text);

    if(mysql_connect($foruminfo['localhost'], $foruminfo['username'], $foruminfo['password']))
    {
        if(mysql_select_db($foruminfo['name']))
        {
            $forumsbl = '';
            if(!empty($foruminfo['forumsbl']))
            {
                $forumsbl = explode(',', $foruminfo['forumsbl']);
                $forumsbl = 'AND forum.forumid != ' . implode(' AND forum.forumid != ', $forumsbl) . ' ';

            }
            // Construct vBulletin SQL for score-based thread title matching
            $sql = 'SELECT thread.threadid,thread.title AS threadtitle, forum.title AS forumtitle, MATCH(thread.title) AGAINST ("' . $text . '") AS score ' .
                   'FROM ' . $foruminfo['prefix'] . 'thread AS thread ' .
                   'INNER JOIN ' . $foruminfo['prefix'] .'forum AS forum ' .
                   'ON ' . $foruminfo['prefix'] . 'forum.forumid = ' . $foruminfo['prefix'] . 'thread.forumid ' .
                   'WHERE (MATCH(thread.title) AGAINST ("' . $text . '")) ' .
                   'AND thread.open <> 10 ' .
                   $forumsbl .
                   'LIMIT ' . $limit;

            $result = mysql_query($sql);
            while ($row = mysql_fetch_assoc($result))
            {
                $url = $title = '';

                // this is an arbitrary number but items less then 4 - 5 seem to be rather unrelated
                if ($row['score'] > 4)
                {
                    $url = str_replace('{thread.threadid}', $row['threadid'], $foruminfo['threadurl']);
                    $url = str_replace('{thread.title}', vb_format_urlsafe($row['threadtitle']), $url);
                    $url = str_replace('{forum.title}', vb_format_urlsafe($row['forumtitle'], array('and','the')), $url);

                    $title = $row['title'];

                    $threads[$row['threadid']]['url'] = $url;
                    $threads[$row['threadid']]['title'] = $row['threadtitle'];
                }
            }
            mysql_free_result($result);
            return $threads;
        }
    }
    return false;
}

/**
 * function vb_format_urlsafe(string $string, mixed $badwords)
 *
 * Makes a string URL safe, and optionally strips an array $badwords
 * (vBSEO removes 'the','and',etc.). Removes invalid characters and replaces
 * spaces w/ dashes. May need to be modified for highly customized URL formats.
 *
 * @param $string       string      // A string of characters to clean up
 * @param $badwords     array       // An array of words to strip
 *
*/
function vb_format_urlsafe($string, $badwords = false)
{
    // Remove forbidden words
    if($badwords)
    {
        foreach($badwords as $key=>$word)
        {
            $string = str_replace($word, '', $string);
        }
    }

    // Convert HTML entities to characters
    $string = html_entity_decode($string);

    // Remove all characters except letters, numbers, dashes, underscores, and spaces
    $string = ereg_replace("[^A-Za-z0-9_ -]", "", $string);

    // Replace multiple spaces with a dash
    $string = ereg_replace(" +", "-", $string);

    // Replace multiple dashes with a single dash
    $string = ereg_replace("-+", "-", $string);

    // Make lowercase
    $string = strtolower($string);

    return $string;
}

>