Have Geek Brain

Returning multiple lines in a string (in Zend Framework)

I have a MySQL database containing the following tables:

sessions
--------
sessionid (INT)
[courseid (INT)]
[locationid (INT)]
[comment (TEXT)]

dates
-----
dateid (INT)
sessionid (INT)
date (DATE)

courses
-------
...

locations
---------
...

Each session has a unique sessionid and each date has a unique datead. But dates do not necessarily have a unique sessionid, as a session can span a variable number of dates (not necessarily sequential).

Selecting each complete row is just a matter of joining tables on the sessionid. However, I am looking for a way to return a rowset for a specific course, where each row in that rowset represents a location and contains a different rowset, each containing one session, which in turn contains a different rowset that contains all dates for this session:

course
    location
        sesssion
            date
            date
        session
            date
            date
            date
    location
        ...

This is because I am querying this database from PHP using the Zend Framework, which has a great interface for manipulating strings and rowsets in an object oriented way.

Ultimately, I'm trying to get the "timetable" out into a view organized first, of course, then the location, then the date. Ideally, I could iterate over each row as location and then, for each location, iterate over each session, and then, for each session, iterate over each date.

I'm going to do this by requesting all locations, sessions, and dates separately. Then I convert each set of strings to an array and add each session array as a member of the locations array and add each date array as a member of the session array.

This is, however, very frustrating and prevents me from handling strings in an object-oriented manner.

I was wondering if there is:

a) the best table schema to represent this data;

b) sql query which I am not aware of;

c) a method in Zend_Db that allows me to assign a rowset to a rowset

Please let me know if I was not clear anywhere, and thanks in advance.

(Finger crossing that it doesn't end at the daily wtf ...)

+1


a source to share


2 answers


I have a lot of problems using Zend Frameworks' database abstraction classes when I have to deal with data from multiple tables. The number of queries fired and the overhead of all generated objects brought my hosting server to its knees. Since then, I've gone back to writing queries to collect all of my data and then looping through the data to create my screen. It's not pretty or OO like using abstraction layers, but it also doesn't make my PHP scripts page to disk just to display a table full of data.



Since Steve mentions which solution you end up with, I would profile your memory usage as well.

+2


a source


You can handle this scenario using the Zend_ Db_ table link functions. You will need to create table wrapper classes for sessions, dates, rates, etc. if you are using Zend_ Db_ Aadpter for your queries currently.

http://framework.zend.com/manual/en/zend.db.table.relationships.html



It is not too different from the approach you described for each dataset separately, but it gives you a direct OO interface to retrieve the relevant related data for a given record.

You will want to benchmark if you go this route as it can potentially fulfill a lot of requests.

+1


a source






More articles:


All Articles