Professional Web Applications Themes

Create VIEW from multiple tables - MySQL

Hello everybody, I want to create a view that holds all rows from a changing number of tables, from which I only some part of the name. Is this possible? In detail: I have an application that generates log entries as mysql rows. It creates for each day of logging a new table: log_20070714, log_20070715, ... Now I want to run statistics on all available log entries, for example the sum of errors (sum(iserror)), so it would be most convienient to have a view holding all log data. Thanks for your help. Christoph...

  1. #1

    Default Create VIEW from multiple tables

    Hello everybody,

    I want to create a view that holds all rows from a changing number of
    tables, from which I only some part of the name. Is this possible?

    In detail: I have an application that generates log entries as mysql
    rows. It creates for each day of logging a new table: log_20070714,
    log_20070715, ...
    Now I want to run statistics on all available log entries, for example
    the sum of errors (sum(iserror)), so it would be most convienient to
    have a view holding all log data.

    Thanks for your help.

    Christoph

    Christoph Guest

  2. #2

    Default Re: Create VIEW from multiple tables

    On Jul 14, 6:09 pm, Christoph Krammer <com>
    wrote: 

    No -- the definition of a view is static. Typically, your schema
    should also be static -- you add rows to tables as the application
    runs, not more tables to the database.

    ZeldorBlat Guest

  3. #3

    Default Re: Create VIEW from multiple tables

    Christoph Krammer wrote: 
    you can create such a view in mysql. there are examples of that on their
    site, i believe but more importantly, this specific action that you're
    explaining may be a better candidate for a stored procedure.



    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  4. #4

    Default Re: Create VIEW from multiple tables


    lark schrieb: 

    I searched their site, but didn't find a thing. My problem here is to
    know the right keywords to search for. "dynamic view", "view on
    unknown tables" don't quite get it. Can you provide a link?

    I got the same problem on a stored procedure, I have to know the table
    name. And additionally, I do not only need the total number, but also
    the number by day, and may other data. So the view I am looking for
    would be the data basis for a lot of stored procedures.

    Thanks and regards,
    Christoph

    Christoph Guest

  5. #5

    Default Re: Create VIEW from multiple tables

    Christoph Krammer wrote: 
    >
    > I searched their site, but didn't find a thing. My problem here is to
    > know the right keywords to search for. "dynamic view", "view on
    > unknown tables" don't quite get it. Can you provide a link?
    >
    > I got the same problem on a stored procedure, I have to know the table
    > name. And additionally, I do not only need the total number, but also
    > the number by day, and may other data. So the view I am looking for
    > would be the data basis for a lot of stored procedures.
    >
    > Thanks and regards,
    > Christoph
    >[/ref]

    Sorry, I agree with ZeldorBlat. Your problem is not creating a dynamic
    view - your problem is creating new tables all of the time.

    Rather, keep your schema static. And when you're done with the rows,
    move them to an archive table or just delete them, depending on your
    requirements.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Create VIEW from multiple tables

    > Sorry, I agree with ZeldorBlat. Your problem is not creating a dynamic 

    I totally agree. A static schema would be the superior solution. The
    simple problem is that I cannot change the logging application. It
    just have to deal with the data I get. The only thing I can imagine to
    get around this problem is to use a trigger to copy each row after
    creation to another table and work on that table.

    Regards,
    Christoph

    Christoph Guest

Similar Threads

  1. Tables don't show in Design View
    By pwalters in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: January 23rd, 05:00 PM
  2. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  3. querying a view of a lot tables
    By Anthony in forum ASP Database
    Replies: 7
    Last Post: August 7th, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139