Search Multiple tables with Relevance

[bdichiara]

I am curious how relevance searching works. I have about 11 tables and I would like to make certain fields in those tables searchable, and the results displayed by relevance.

I've thought about this many different ways to do this, but haven't come up with a way that seems the simplest as well as provides the best performance.

See, I would like each of my search results to display differently based on what table they are coming out of. For example, if a user searches for "Accounting", I would like to hit the "departments" table and find accounting, then display the phone/fax numbers to that department, then move onto the employees table and find the employees that are in the accounting department, showing their name, extension, and title, and then finally go into our table of documents and display any documents with "accounting" in the designated text search field.

What I'm asking for may be too complicated, however I feel this is going to provide the best results for our users when they use the search box.

If all this sounds like too much to try to accomplish, then how could I just do a search through each of my tables and return the results by % of relevance?

Thanks for the help.

_______________
_brian.

 

[bdichiara]

ok, actually, I have read about FULL TEXT searches here:

http://www.phpfreaks.com/tutorials/129/0.php

I have things setup to search by relevance now, but I guess what I need is a way to do what i mentioned in the 3rd paragraph.

My idea is to search each table, output each row in a formatted way and insert it back into a temporary table. Then output that table in order of relevance and then drop the table.

does that sound like too much work for the database? will there be negative results by doing it this way? Is there a better way?

Thanks.

_______________
_brian.

 

[bdichiara]

OK, actually, I think i pretty much got a handle on this. I will start another thread for my next question.

_______________
_brian.