[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