Related topics for non-vB pages

This isn't really a hack, more of an add-on for vBulletin.

What I'm trying to do is create a method of displaying related topics at the end of articles for NissanPerformanceMag.com. This isn't the same as the "Last XX Posts on non-vB page" hack. Instead, it uses some specified keywords to display 10 discussion topics related to the subject of the article. I haven't been able to locate any similar projects, so I attempted it myself.

It's easy to get lost in the search.php file (at least for me ), but I was able to figure out enough to get this far. It displays the queries used, as well as the final results. The problem is it doesn't work very well with multiple words, such as this:

I can't figure out how to make it display threads containing all of the search words. Right now it uses "OR" instead of "AND", if you can see what I mean. It also doesn't give any weight to results. If anyone can offer some insight into methods of improving this I would appreciate it very much! What I would like to do (using the second link as an example), is display threads containing all the search words instead of simply displaying threads that contain just one.

Here's the code:

PHP Code:

<?php
mysql_connect("localhost", "username", "password");
$words = strtolower($_GET['query']);
$words = explode(' ', $words);
for ($i = 0; $i < count($words); $i++) {
  $searchwords .= "title = '{$words[$i]}'";
  if ($i+ 1 < count($words)) {
    $searchwords .= ' OR ';
  }
}
$query = "SELECT wordid FROM word WHERE $searchwords";
echo "<p>$query";
unset($searchwords);
$result = mysql_db_query("forums", $query);
if (mysql_num_rows($result) >= 1) {
  $i = 1;
  while ($r = mysql_fetch_array($result)) {
    if ($i > 1) {
      $searchwords .= ', ';
    }
    $searchwords .= "'{$r['wordid']}'";
    $i++;
  }
  $query = "SELECT DISTINCT postid FROM searchindex WHERE wordid IN($searchwords)";
  echo "<p>$query";
  unset($searchwords);
  $result = mysql_db_query("forums", $query);
  $i = 1;
  while ($r = mysql_fetch_array($result)) {
    if ($i > 1) {
      $searchwords .= ', ';
    }
    $searchwords .= "'{$r['postid']}'";
    $i++;
  }
  $query = "SELECT threadid FROM post WHERE postid IN($searchwords) ORDER BY dateline DESC";
  echo "<p>$query";
  unset($searchwords);
  $result = mysql_db_query("forums", $query);
  $i = 1;
  while ($r = mysql_fetch_array($result)) {
    if ($i > 1) {
      $searchwords .= ', ';
    }
    $searchwords .= "'{$r['threadid']}'";
    $i++;
  }
  $query = "SELECT threadid, title, replycount, views FROM thread 
  WHERE threadid IN($searchwords) ORDER BY lastpost DESC LIMIT 10";
echo "<p>$query";
  $result = mysql_db_query("forums", $query);
  while ($r = mysql_fetch_array($result)) {
    echo "<p><a href=\"http://www.nissanforums.com/showthread.php?threadid={$r['threadid']}\">{$r['title']}</a>";
  }
}
mysql_close();
?>

[ScottA]

Well, I did eventually figure out the problem. It just involved rewriting a couple of the queries. I removed the link from the previous post because I've since deleted the file.

[SteveK]

This sounds like what I'd like to implement. Can you share with us the final hack?

Thanks

Steve

[ScottA]

Sure. Here's what I came up with. The only real differences between my first attempt and this final version are the third and fourth MySQL queries. It does not return the most recent results. What it does instead is group the threads with the highest number of instances of the search word(s), and then sort those by date. It may not be the most efficient or desirable system for your particular application, but it seems to work pretty well. If you come up with any improvements please let me know.

I've simply included it as a function, which can be called with some keywords separated by spaces. I'd recommend not using more than 2 or 3 keywords simply because of the large number of posts it will find. If you print out the queries like I did in my first example you'll see just how many! Change "LIMIT 5" to the number of results you want.

PHP Code:

related_threads("keyword1 keyword2 etc"); 


Related Threads:

PHP Code:

<?php
function related_threads($keywords) {
  @mysql_connect("localhost", "username", "password");
  $words = strtolower($keywords);
  $words = explode(' ', $words);
  for ($i = 0; $i < count($words); $i++) {
    $searchwords .= "title = '{$words[$i]}'";
    if ($i+ 1 < count($words)) {
      $searchwords .= ' OR ';
    }
  }
  $query = "SELECT wordid FROM word WHERE $searchwords";
  unset($searchwords);
  $result = @mysql_db_query("forums", $query);
  if (@mysql_num_rows($result) >= 1) {
    $i = 1;
    while ($r = @mysql_fetch_array($result)) {
      if ($i > 1) {
        $searchwords .= ', ';
      }
      $searchwords .= "'{$r['wordid']}'";
      $i++;
    }
    $query = "SELECT DISTINCT postid FROM searchindex WHERE wordid IN($searchwords)";
    unset($searchwords);
    $result = @mysql_db_query("forums", $query);
    $i = 1;
    while ($r = @mysql_fetch_array($result)) {
      if ($i > 1) {
        $searchwords .= ', ';
      }
      $searchwords .= "'{$r['postid']}'";
      $i++;
    }
    $query = "SELECT threadid, count(threadid) AS count FROM post WHERE postid IN($searchwords) AND visible = '1' 
GROUP BY threadid ORDER BY count DESC, dateline LIMIT 5";
    unset($searchwords);
    $result = @mysql_db_query("forums", $query);
    $i = 1;
    while ($r = @mysql_fetch_array($result)) {
      if ($i > 1) {
        $searchwords .= ', ';
      }
      $searchwords .= "'{$r['threadid']}'";
      $i++;
    }
    $query = "SELECT threadid, title, replycount, dateline FROM thread WHERE threadid IN($searchwords) AND visible 
= '1' AND open = '1' ORDER BY lastpost DESC";
    $result = @mysql_db_query("forums", $query);
    echo "<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" width=\"100%\">\n  <tr>\n    <td>Thread</td>\n    
<td>Date</td>\n    <td>Replies</td>\n  </tr>\n";
    while ($r = @mysql_fetch_array($result)) {
      echo "  <tr>\n    <td><a href=\"http://www.nissanforums.com/showthread.php?threadid={$r['threadid']}\" 
target=\"_blank\">{$r['title']}</a></td>\n    <td>" . date("m-d-y", $r['dateline']) . "</td>\n    <td>{$r['replycount']}
</td>\n  </tr>\n";
    }
    echo"</table>\n";
  }
  @mysql_close();
}
?>

Edit: I wrapped some of the lines so they'd fit on my screen...

Scott

[DrkFusion]

Hey nice mag, how can I subscribe :O)
-Arunan

[ScottA]

It's online only at this time. Eventually we want to have it in print, but we're not there yet.

[mt_100]

How are you calling the function from the search form? I can't seem to get it right, can I have a copy of your search form?

[ScottA]

A search form is not required. Call the function from within your PHP file with some words and it outputs the appropriate HTML. This isn't intended to be used as a search program, but rather a method of displaying related forum topics at the end of articles for NissanPerformanceMag.com. The example I gave above is exactly how it's used for my own situation. I just change the search words as appropriate.