searching more than one table

Hi

I am looking for some general pointers about how to go about searching more than one table, the tables are currently unrelated. The search is a general site based search, so a user can can find an item in any section of the site.

The site is news based and as such the tables are quite content heavy. I am using mysql fulltext at the moment, but I can not get it to work on more than one table, so my thoughts were to look at the way my tables were related.

Should there be a general table_list table that contains all other tables, relating them under this table? this is where my concepts fall down, as how does this aid in searching on the specific fields in the other tables?

Any help much appreciated.

Thanks

Buttie

If your tables aren't connected in some way you run into a problem :-). You can join them with a cross join (e.g. "select * from table1, table2") but then you get ALL records from table1 combined with ALL records from table2.

You could use the metadata (which should be available on every database since this is one of the main rules of a database, each metadata is defined in tables (so your database should already have a table of tables in your database (e.g. sysobjects on MSSQL))) but there you still have the problem how to join your indiviual records.

On each project i worked on, when it comes down to searching there was always a relation between the different tables where we could join them. Other way (hack ;-)) would be to don't let the user search in a such generic way, but just let him choose which section he wants to search...