troubleshooting Question

Build "grouped" array from DB results

Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America asked on
PHP
18 Comments1 Solution345 ViewsLast Modified:
In trying to itrerate through the results of an ExpressionEngine DB Query that returns this result:
"ProgramGroup","ProgramTitle","CourseDesc","CourseCode","CourseTtle"
"A",           "A Title",     "Z Title",   "Z",         "Z Desc"
"A",           "A Title",     "Y Title",   "Y",         "Y Desc"
"B",           "B Title",     "X Title",   "X",         "X desc"
"B",           "B Title",     "V Title",   "V",         "V desc"
"C",           "C Title",     "U Title",   "U",         "U desc"
"C",           "C Title",     "T Title",   "T",         "T desc"
"D",           "D Title",     "S Title",   "S",         "S desc"
"D",           "D Title",     "R Title",   "R",         "R desc"
"D",           "D Title",     "Q Title",   "Q",         "Q desc"
and build an array that will be output as JSON like below..
This is the expected output:
[{"group":"A","title":"","desc":"A Title","programs":[{"code":"Z","title":"Z Title","desc":"Z Desc"},
                                                      {"code":"Y","title":"Y Title","desc":"Y Desc"}]},
 {"group":"B","title":"","desc":"B Title","programs":[{"code":"X","title":"X Title","desc":"X Desc"},
                                                      {"code":"V","title":"W Title","desc":"W Desc"}]},
 {"group":"C","title":"","desc":"C Title","programs":[{"code":"U","title":"U Title","desc":"U Desc"},
                                                      {"code":"T","title":"T Title","desc":"T Desc"}]},
 {"group":"D","title":"","desc":"D Title","programs":[{"code":"S","title":"S Title","desc":"S Desc"},
                                                      {"code":"R","title":"R Title","desc":"R Desc"},
                                                      {"code":"Q","title":"Q Title","desc":"Q Desc"}]}]

This is my first try and it is not returning expected results.
$results = $this->EE->db->query($sql);
$program_group = "";
if($results->num_rows() > 0) {
    foreach($results->result_array() as $row) {
        if($program_group != $row["ProgramGroup"]) {
            $content["group"] = $row["ProgramGroup"];
            $content["title"] = "";
            $content["desc"]  = $row["ProgramTitle"];
            $program_group    = $row["ProgramGroup"];
        } else {                
            $content[$row["ProgramGroup"]]["programs"]["code"]  = $row["CourseCode"];
            $content[$row["ProgramGroup"]]["programs"]["title"] = $row["CourseTtle"];
            $content[$row["ProgramGroup"]]["programs"]["desc"]  = $row["CourseDesc"];
        }
    }
}
ASKER CERTIFIED SOLUTION
Eddie Shipman
All-around developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros