Full Text Searching - Thoughts

condoug

I am building a website which consists of many different mysql tables for different modules within the site. I have Full Text indexed certain fields across the multiple tables.

Now I am after any thoughts or suggestions on the approach to searching each table and outputting the results showing the best matched records at the top of my page (as you would expect).

My initial thought is to simply post the search criteria string and run X number of separate queries, one for each table in my database. Therefore I need to search through and output the results from each table in a loop and then move onto the next table, do the same and so on.

The problem with this approach is that, for example, if I search for the keyphrase 'very large red book' and I there is a 100% perfect match in a record in my 4th table, then the result for this will not be at the top of my page, it will only be at the top of the results for the 4th table/query!

So, in a nutshell, any thoughts on how I can search X number of tables using FULL TEXT and output the best matched results form across all tables in 'best matching order' ?

Thanks for reading.
Doug

dagon

in a similar situation with multiple db's and tables i have a search table that is populated from all the other tables, so i just search that. may not be practicable depending on your circumstances.

condoug

Unfortuately its not the best methed for my site, I am trying to keep my keyword tags stored in the same record as the data which is being searched.

Storing it in a separate table will create far to much development within the CMS.

So frustrating!

johanafm

Query each table separately, fetchAll, array_merge, array_multisort.