I've got a MySQL database that contains these tables:
sessions -------- sessionid (INT) [courseid (INT)] [locationid (INT)] [comment (TEXT)] dates ----- dateid (INT) sessionid (INT) date (DATE) courses ------- ... locations --------- ...
Each session includes a unique sessionid, and every date includes a unique dateid. But dates don't always possess a unique sessionid, like a session can span on the variable quantity of dates (not always consecutive).
Choosing each full row is only a few joining the tables around the sessionid. However, I am searching for a method to return a rowset for the courseid, where each row for the reason that rowset signifies an area, and consists of another rowset, each that contains single session, which consists of another rowset, which consists of all the dates for your session:
course location sesssion date date session date date date location ...
The reason being I am using querying this database from PHP using Zend Framework, with a great interface for adjusting rows and rowsets within an object-oriented manner.
Ultimately, I am attempting to output a 'schedule' towards the view, organized first by course, then location, then date. Ideally, I'd have the ability iterate over each row like a location, as well as each location, iterate over each session, as well as each session, iterate over each date.
I am considering carrying this out by querying for the locations, periods, and dates individually. Then, I'd convert each rowset into an assortment, and add each periods array as part of a locations array, and add each dates array as part of a periods array.
This, however, feels very kludgy, and does not provide me having the ability to handle the rows within an object-oriented manner.
I'm wondering if there is either:
a) a much better table schema for representing this data
b) an sql query which i am unaware of
c) a technique in Zend_Db that enables me to assign a rowset to some rowset
Please tell me basically weren't obvious anywhere, and thanks ahead of time.
(Crossing my fingers this does not finish on the daily wtf...)
I have encounter plenty of difficulties with using Zend Frameworks database abstraction classes after i suffer from data from multiple tables. The amount of queries running and also the overhead of all the objects produced has introduced my hosting server to it's knees. I have since reverted to writing queries to collect all my data after which walking the information to construct my display. It isn't quite a or OO as while using abstraction layers but it is also not making my PHP scripts page to disk simply to display a table filled with data.
As Steve mentions benchmark whatever solution you finish track of, I'd also profile your memory usage.
You can handle this while using relationship options that come with Zend_ Db_ Table. You'd have to create table wrapper courses of instruction for periods, dates, courses, etc. if you are using Zend_ Db_ Aadpter for the queries presently.
It isn't too not the same as the approach you referred to of querying for every dataset individually, but it offers a superior a easy OO interface for locating the right related data for any given record.
You will want to perform some benchmarking should you follow this path, as it may potentially execute lots of queries.