View Full Version : Multi-Table Fulltext search

04-26-2009, 12:12 AM
Im trying to write a forum search using MYSQL's fulltext feature.

My goal is to be able to search multiple tables, order the results by relevance and display them.

So if I have the tables Users, Topics, and Posts lets say a user searched for the word Forum

It would look in all the tables Users (username/displayname), Topics (title) and Posts (message)

and order them by relevance.

So that query would allow me to return on my page something like:

User: Forum
Topic: Forums
Post: blah blah forum
Post: blah blah forum blah
Topic: This forum is totally awesome!
User: forumname is lame

I know how to do this for each table, but I want to have the tables connected and ordered by relevance.

This is similar to facebook's search. If you search for a term, it will look in users, groups, events, applications, pages, etc and display the results based on relevance.

04-26-2009, 08:14 PM

05-05-2009, 05:15 PM
What about searching for more than one word? I have developped a search engine for my forums but I can't think of a way to search a message body for any two terms the user specifies...

05-05-2009, 05:46 PM
MySQL's FULLTEXT binary search functionality gives you the ability to use Google-like search features: "+searchword +searchword2 -searchword3" will give you everything that contains the first two keywords that doesn't contain the third keyword.

05-09-2009, 11:49 AM
Fumigator, the issue I see with union is that the columns are different for the different tables, and I want to know what table it came from. For example user results are formatted differently from topic results and from post results and they contain different column names.