A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearQuerying data from multiple clustered tables as one unified - page 1
User InfoPosts
Querying data from multiple clustered tables as one unified#1
I(ve recently started work on a project which involves creating a web-based reporting interface for a fairly old software responsible for managing some access control hardware like electronic door locks for example.
I have chosen CakePHP for the task and all it involves is querying the database for the log records and displaying them on the website. The software is written in C++ and uses MSDE (a scaled-down version of Microsoft SQL Server 7.0 or 2000) and I was able to configure CakePHP so that it successfully connects to the DB.

The problem is that the developer of this software has chosen to store the logs in the database by creating a separate table for each day. So it looks like this:

tbl_DoorEvent_2008_08_07
tbl_DoorEvent_2008_08_08
tbl_DoorEvent_2008_08_09
tbl_DoorEvent_2008_08_10
...


I am not really familiar with MSDE but I still doubt that it is an acceptable practice to design and maintain a database like this. I do not know how the desktop software manages to parse query all the information when it(s all in this format but my CakePHP application is having some really hard time chewing this up.

I want to stick with MVC for the web application but I don(t know how to implement the DooreventModel in CakePHP so that it gets the information from all the partitioned tables.
I(ve tried implementing some hacks like using

UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_07
UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_08
....


and this sort of works but when I want to perform WHERE restriction on the whole query or any other SQL operator it doesn(t work.

Also one of the requests was that the web-based application does not add or create any additional databases or tables and only uses the existing ones to display the reports so creating one huge table that includes all of the clustered ones is not really an option.

I just hope that someone will come up with an SQLServer function that will merge all tables into one for me every time I perform a query but I know this is a bit optimistic, so I(m open to all suggestions really.
And please remember that the solution must work with CakePHP and as a single Model in the MVC

posted date: 2009-04-12 06:15:00


Re: Querying data from multiple clustered tables as one unified#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-12 06:15:01


Re: Querying data from multiple clustered tables as one unified#3
plzsendzmetehcode?

posted date: 2009-04-12 06:23:00


Re: Querying data from multiple clustered tables as one unified#4
Mitch, what code ?

posted date: 2009-04-12 06:27:00


Re: Querying data from multiple clustered tables as one unified#5
You probably want a partitioned view?

posted date: 2009-04-12 06:29:00


Re: Querying data from multiple clustered tables as one unified#6
You can use a where statement if you place the unions in a subquery:SELECT *FROM ( SELECT * FROM tbl_DoorEvent_2008_08_07 UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_08) subwhere sub.MyKey = MyValueOr create a view:CREATE VIEW vw_MyViewASSELECT * FROM tbl_DoorEvent_2008_08_07UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_08After that you can query the view:SELECT * from vw_MyView where MyKey = MyValueP.S. Never use * in production queries, espcially not views.

posted date: 2009-04-12 06:55:00


Re: Querying data from multiple clustered tables as one unified#7
Partitioned views on MSDE?

posted date: 2009-04-12 07:30:00


Re: Querying data from multiple clustered tables as one unified#8
Yes. Because in this case it's just a sound name for a simple UNION ALL view, which, because of the strange table schema, is a partitioned view. It has nothing to do with 'true' partitioned tables and managing them in advanced ways. See, the answer above is upvoted despite it says the same as mine.

posted date: 2009-04-12 09:01:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.