Query ALL Tables in a Database

[piskie]

***EDITED BY O~Snapple PLEASE USE TAGS WHEN POSTING CODE***
I have a site that stores each individual merchants' products in his own Table within a single database for the whole site.

What I need to achieve is a site search that will query ALL Tables within the one Database. The tables are identical in structure.

The Tables are sequentialy named from T101 upwards.
Curent Queries similar to:

PHP Code:

$query_LISTALL = "SELECT * FROM T111 ORDER BY auto ASC"; 


I need (or would very much like) the query to be unaffected by addition and/or deletion of Tables as Merchants come and go.

There would be no problem leaving a Table intact but empty instead of deleting.

I envisaged some sort of wild card/s within the query or a from/to expression, but I don't know whether MYSQL will handle that in the context of Tables. Nice if it can.

Please keep any reply in simple terms as this is my first database driven site.

[saidev]

***EDITED BY O~Snapple PLEASE USE TAGS WHEN POSTING CODE***
So you are wanting to do "SELECT * FROM T% ORDER BY auto ASC" (where % is wild card). I am pretty sure you can't do that. Can you write some code to create they query dyncmially? First find out all the merchants tables you can want to search, like

PHP Code:

$merchants_from_part = "T100 t100, T200 t200" 


than create

PHP Code:

$merchants_select_part = "t100.*,t200.*" 


and than use

PHP Code:

$query_LISTALL = "SELECT ".$merchants_select_part." FROM ".$merchants_from_part ." ORDER BY auto ASC"; 


I must add this...what you mention above sounds like a bad design. Why not create a table with merchants ids and use the T100 table as the base table and add the merchants id column. One thing this could do is you improve lots of preformance while keeping your database normalized.

[arvind.d]

i accept with saidev. with this design there is no point in using a database. you might as well use files for doing this, as they were doing ages ago. and by the way, if you start naming your tables from T101, how would you identify which table is for which merchant?

[piskie]

Thanks for your input arvind.d

However, there is a section (folder) for each merchant where the results will be sorted from each merchants own table.
In addition, from the site (root) home page, there is a requirement to search accross ALL merchants tables.

There are reasons why the tables will be numbered consecutively from T101 upwards and also knowing which merchant is associated with which table is not an issue.

Neither of the above points are relevent to my requirement and request for help to achieve data rertrieval accross all tables.

[Krishnaswamy]

Try a Union ALL

[websiterepairguys]

This is possibly the WORSE database design EVER.

Use one table with the merchant_id field

Man I feel sorry for the poor sap that has to maintain that.