php and mysql full text search across multiple tables using a relevancy score
Wednesday, June 24th, 2009the setup
I am currently building a paltfrom to host my air applications on. this is plattform includes a feedback and bug tracking forum application where users can post their thoughts and problems. In order for this to be helpfull i needed to implement full text search. the forum constists of basically two tables.
- 1.) threads (title,date,author_id,forum_id,threadComment_id)
- 2.) comments (text, author, date,thread_id)
so the way this works is that a user can open up a thread , which stores a new thread and a comment in the database. the two are linked by adding the thread Id to the comment and the comments unique id to the thread. any futur comments that are posted to this thread will simply have the thread Id stored in them.