Php – Returning multiple rows per row (in Zend Framework)

databaseMySQLPHPsqlzend-framework

I have a MySQL database containing these 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 dateid. But dates don't necessarily have a unique sessionid, as a session can span over a variable number of dates (not necessarily consecutive).

Selecting each full row is simply a matter of joining the tables on the sessionid. However, I'm looking for a way to return a rowset for a particular courseid, where each row in that rowset represents a location, and contains another rowset, each containing single session, which in turn contains another rowset, which contains all of the dates for that session:

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

This is because I'm using querying this database from PHP using Zend Framework, which has a great interface for manipulating rows and rowsets in an object-oriented manner.

Ultimately, I'm trying to output a 'schedule' to the view, organized first by course, then location, then date. Ideally, I'd be able iterate over each row as a location, and then for each location, iterate over each session, and then for each session, iterate over each date.

I'm thinking of doing this by querying for all the locations, sessions, and dates separately. Then, I'd convert each rowset into an array, and add each sessions array as a member of a locations array, and add each dates array as a member of a sessions array.

This, however, feels very kludgy, and doesn't provide me with the ability to handle the rows in an object-oriented manner.

I was wondering if there was either:

a) a better table schema for representing this data;

b) an sql query which i'm 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 haven't been clear anywhere, and thanks in advance.

(Crossing my fingers that this doesn't end up on the daily wtf…)

Best Answer

I've run into lots of issues with using Zend Frameworks database abstraction classes when I have to deal with data from multiple tables. The number of queries that run and the overhead of all of the objects generated has brought my hosting server to it's knees. I've since reverted back to writing queries to gather all of my data and then walking the data to build my display. It's not a pretty or OO as using the abstraction layers but it's also not making my PHP scripts page to disk just to display a table full of data.

As Steve mentions benchmark whatever solution you end up with, I'd also profile your memory usage.

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Answer

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Mysql – concatenate multiple MySQL rows into one field

Related Topic