Search Code for a Forum

[marc_gfx]

I programmed my own forum a while ago, quite similar to this one in a way (hierarchical, new threads on each level as well as posts). most people will probably ask why I did this... well, I simply wanted to. however there are still some things missing to make it perfect. I currently have no forum-search and am now wondering how to go about this sensibly. Thought I'd get some feedback on my ideas...

My website is for numerous different teams with their own forums that should therefore not interfere with each other.
I plan to index every unique word from every post, that is larger than 2(or 3?) letters.
the structure would be something like this:
{
teamID
word
list of all postIDs separated by a special character
}
e.g.:{
20,
"hello",
11*22*23*44*55*75*78
}
meaning that "hello" is found in 7 different posts. therefore searching for "hello" would give me the list of all relevant posts fairly quickly. searching for "hello" and "bye" would be done by searching for both words separately and then looking for overlapping posts or joining results depending on what search commands were used.

seems to be a sensible way to me, but I have absolutely no experience in search-engine methods. maybe someone can give me a kick in a better direction before I end up producing garbage.

[StupidScript]

How about indexing the entry fields using your db's indexing function, and then doing a pretty simple query, i.e.

..from entries where teamid='$teamid' and entry like '$term'...

You'll probably want to rank the results, so there's more to it ... but that might get you started.

[Capo64]

i would do something like what stupid script said except like this

$posts = array();
$result = mysql_query("SELECT * FROM entries WHERE teamid='" . $teamid . "'");
while($row = mysql_fetch_array($result))
{
$posts[$postID] = $row['entrycontent'];
}
//Now you have an array with all of the posts for that team
foreach ($posts as $id => $post){
$newposts[$id] = substr_count($post, $searchterm);
}
//$newposts is now an array containing each post ID => the amount of times the search term was found in that ID
//order $newposts by value and display it

Kinda just typed that up and i've never really tried to make a search engine so it's prolly sketchy, but that's what I would do.

[marc_gfx]

@stupidscript: thats kind of the original idea I had. But then I was thinking what would happen if i index words like "the" or other very common words. I would have tons and tons of entries that would be searched. I was thinking it could save search-time to really limit the amount of available entries and pre-group the results. I could of course use 2 tables for this, one that stores the word name and one that identifies the posts where the word is used.
table1(entryID, teamID, word) table2(entryID, postID).
the other method I came up with would surely reduce the amount of entries stored, but would have this long tail of results in some cases. the idea would then be to cancel out words that lead to inconclusive search results automatically (e.g. "the") , if they are found in more than 10% of all posts. But it could be slow combining results... I guess I'd need to do some testing.

@capo64: that idea would definitely be rather slow. just imagine there are 2000 posts that would be searched full-text every query... the amount of times something is found doesn't seem to be relevant to me either. I'm considering simply searching by term and then sorting by time (the newer the higher...)

thanks guys for your input!