July 16th, 2013, 02:44 AM
Data fetching from multiple dynamic created table
I want to select rows from dynamic created tables i.e we have tables like web_analytics_mm_yy(ex: web_analytics_06_13, web_analytics_05_13, web_analytics_04_13 and so on)
- web_analytics ->common name for all tables
- mm_yy -> month_year( Will be created each month by cron)
HOW to construct SQL STATEMENT USING UNION ALL?
is there any thing that can be done using mysql Query only?
and i want to know what is the best method to fetch data from multiple tables which are created dynamic.
July 16th, 2013, 05:54 AM
using PHP you could do a query such as:
SHOW TABLES LIKE 'web_analytics_%'
then get the result and use that to create a union query..
in pure mysql, i guess you need a procedure/cursor?
July 16th, 2013, 06:17 AM
Oh, no ...
Your problem is that you even have those tables. Whoever designed the database doesn't know what he's doing. No, you do not store grouped data by generating hundreds of tables all with the same structure but a different name and then piece them all together with weird metaprogramming hacks.
I strongly advice you to throw away the current structure, send the programmer to some training for database basics (or let somebody else do the job) and then start all over. Yes, that's painful. But it will be even more painful, time-consuming, money-wasting and error-prone to maintain the current crap. The database layout is broken by design.
If you're not sure how to do it right, the database people will help you.
Comments on this post