Searching many tables

[kbc1]

Hi all,

I am looking to create a search facility within my website, but rather than providing a search page with drop down boxes for filtering, I simply want to have a search box at the top of the page which is always visible and would take a keyword and retrieve the results as you would expect.

However, my website content is divided into lets say 10 different MySQL tables (table1, table2, table3 etc each with an introduction (TEXT) field and a description (TEXT) field which are the fields containing my content).

Can anyone offer advice on how is the best way to approach this kind of searching i.e. what is the best way to build the query string?

Would I have to have a SELECT query for EACH table I have within my database or is there a better method?

Thanks in advance.
K.

[saraadmin]

A couple of things to consider:

*) MySQL provides FULLTEXT indexes but only on single tables (i.e. it won't mix more than one table in an index).

*) You can, of course, use a SELECT statement collecting fields from mutiple tables and use WHERE filtering on those fields. But do you want to use OR logic for each field or AND logic?

*) I hope I'm not being rude, but are you sure you actually need lots of tables, or would you be better off with lots of rows and one table?

*) If your data is not tabularly structured, should you consider an document-centric approach using, for example, XML?