Search Multiple Tables at in one query (MySQL/PHP#

Question

I have about 15 tables, each table containing about 10, 000 rows and about 30 columns. I want the users of my site to be able to search for a Part Number, or a product/description and display the results. Regardless of which table it's in.

Each product page is the same as the name of each corresponding table. So if a product is found, it will just display a link to the correct page (the filename is the name of the table).

Can't we just do something like:

query = "find $q in dbase.tables"; ?

And if $q exists in a table, return the name of the table it was found in?

Any help is appreciated!

Thank you

Answer 1

If all the tables have the same structure you might be able to do that. (That sounds like it's a simplistic manual partitioning scheme.)

Anway to join multiple tables you can use

SELECT * FROM tbl1
UNION ALL
SELECT * FROM tbl2
...

And to get your simple access method you might create a view on that concatenation:

CREATE VIEW alltables AS
    SELECT * ... UNION ... 

Not ever tested this. But this view would then facilitate SELECT * FROM alltables WHERE find($q)... - you'd still need a valid query for the columns of course.

Answer 2

why do u have so many tables? why not have only one table of part #'s, than another table of descriptions and join on the part number?