#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    17
    Rep Power
    0

    Data fetching from multiple dynamic created table


    Hi All,

    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.
    Thx ppl
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,031
    Rep Power
    377
    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?
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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

    • paulh1983 agrees : agree. thanks for always pointing to right direction and not just simply "answering" the q like i did.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo