I'm one of the billion people developing a discussion forum software. I am having some problems with my search feature though. I've read up on fulltext indexing in the MySQL documentation, but I still don't get how I am to solve the problem. First some needed db structure info (irrelevant columns not showed):
table topics:
id mediumint(8) unsigned NOT NULL auto_increment,
poster varchar(25) NOT NULL default '',
subject varchar(70) NOT NULL default '',
lastpost int(10) unsigned NOT NULL default '0',
forumid smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (id),
FULLTEXT KEY subject (subject)
table posts:
id mediumint(8) unsigned NOT NULL auto_increment,
poster varchar(25) NOT NULL default '',
message text NOT NULL,
topicid mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (id),
FULLTEXT KEY message (message)
As you can see I have fulltext indexes on the subject column in topics and on the message column in the posts table. I want users to be able to search in subjects or message bodies tied to a specific forum (forumid). I'm not sure whether they should be able to search in both at the same time. It all depends on wheter I can work this out first.
Searching in only subjects is no problem. I took the following query more or less straight out of the MySQL docs:
SELECT id, poster, subject, lastpost, MATCH ('subject') AGAINST ('some words') AS score FROM topics WHERE forumid=blaha AND MATCH ('subject') AGAINST ('some words')
This returnes all the topics ordered nicely according to relevance. The problem is when I want to search in message bodies.
I want to display the search results pretty much like it is displayed in this forum. That is information from the topic and not the post itself. If I run a query similar to the one above I will be able to obtain a list of the posts that matched the search, but then I would have to go through all those posts and fetch the topic to which they are tied. This way I can get duplicates (more than one hit in one thread) and will have to deal with that. It would also be very slow since I would have to run one query per returned post to obtain the topic. I have no idea how I should do this. I'm no master at SQL, but I guess it could be solved with some kind of join.
The posts table has no forumid so just browsing through the posts table gives no indication as to what forum a specific post is tied to.
I'm not sure I got all the info in there. If you don't have a clue as to what my problem is, please tell me and I will try to explain further.
Regards
Rickard Andersson
