Reformat PHP MYSQL Array Result Multiple Levels Deep

Question

I reformatted this question hoping that the duplicate marking of this question will be changed as that answer doesn't go as far as this need does. It is not clear how to get past the first level of SECTIONS containing all CHAPTERS which the other question explains how to do.

I'm trying to first group by SECTION, then by all the CHAPTERS in that section and then all the PAGES within each chapter. Hope someone can help me.

Here is the result I get back from MYSQL in PHP:

I'm trying to format the array to look like this:

I have attempted several solutions, this is the closest I get, but the groupings are separated in the results:

    foreach ($book_content as $record) {

        $temp_array['SectionName'][] = $record['BookSection'];
        $temp_array['Chapters'][] = array('ChapterTitle' => $record['ChapterTitle']);
        $temp_array['Chapters']['Pages'][] = $record;
    }

echo '<pre>'; print_r($temp_array);
die();

Which gives me:

A second attempt gets me the right structure, but the like-values are not grouped. In other words, each time a page is included in Section 1 > Chapter 1 it shows as unique array instead of grouped within the Section 1 > Chapter 1 array.

$temp_array = array();
$i = 0;
foreach ($book_content as $record) {

    $temp_array[$i]['SectionName'] = $record['BookSection'];
    $temp_array[$i]['Chapters'][$i]['ChapterTitle'] = $record['ChapterTitle'];
    $temp_array[$i]['Chapters'][$i]['Pages'][$i] = $record;
    $i++;
}

You need to work at array level first. Please edit your question to include how does the MySQL result array looks like in your case — Madhur Bhaiya, Sep 27 '18 at 18:07
It would be better to show us how you create the first json then we can fix it at source rather than fiddle with it after the fact — RiggsFolly, Sep 27 '18 at 18:07
Based on the JSON result I'd probably recommend storing your data in MongoDB or CouchDB where you can save the records as JSON documents. — Eric Kigathi, Sep 27 '18 at 18:17
@MadhurBhaiya yes, good suggestion. I will update the question when I'm back at my desk. Thank you for responding. — Jeff Solomon, Sep 27 '18 at 19:11
@MadhurBhaiya I reformatted the question with PHP/MYSQL arrays vs. JSON. I also looked at that question that said this was a dupe of, it gets me closer, but it's only one level deep and I am 3 levels deep (i'm so JV, sorry). My final output is SECTION > CHAPTER > PAGES. Thx for help. — Jeff Solomon, Sep 29 '18 at 06:09
@RiggsFolly I updated the question to show this. I still cannot figure out how to do it with the question this is marked as duplicate. That question only takes the array 1 level deep, my situation is trying to group by SECTION, then CHAPTERS, then PAGES. I'd greatly appreciate some guidance still. Thanks. — Jeff Solomon, Sep 29 '18 at 17:05
@Barmar I'm really stuck on this, can you please review my edits and consider to mark as not a duplicate of that other questions. — Jeff Solomon, Sep 30 '18 at 22:53
If you want help, you need to post the code that shows that you made an attempt. It's essentially the same question, you process the deeper levels exactly the same way. — Barmar, Oct 01 '18 at 00:10
@Barmar added my current attempted solution that gets me closest. — Jeff Solomon, Oct 01 '18 at 18:50
Your code makes no attempt to group related rows together, like in the duplicate question. It seems like you made no attempt to understand the general idea. But I've reopened the question. — Barmar, Oct 01 '18 at 19:15
yes, clearly I don't understand the general idea, but I made many attempts. It's pretty hard being new to programming and on this site, people are often not that understanding or just outright mean. If you have any recommendations on articles that better outline how to loop through arrays to format in various ways I'd appreciate the guidance. But I appreciate you re-opening it nevertheless. — Jeff Solomon, Oct 01 '18 at 19:28
Programming isn't about reading articles that tell you how to do things, it's about thinking logically about the problem and working it out. — Barmar, Oct 01 '18 at 19:36

score 1 · Accepted Answer · answered Oct 01 '18 at 19:33

You need to use associative arrays that are keyed off the section names and chapter numbers, so you can group these related rows together. You're just adding a new element in the result for each element in the original array, which doesn't do any regrouping.

You go through the original array, checking whether there's already an entry for the section or chapter already in the result. If not, you add an initial entry for it, then fill it in with the details.

$result = array();
foreach ($book_content as $record) {
    $section = $record['BookSection'];
    if (!isset($result[$section])) {
        // Create initial entry for this section
        $result[$section] = array(
            'SectionName' => $section,
            'Chapters' => array());
    }
    $chapter = $record['ChapterNumber']
    if (!isset($result[$section]['Chapters'][$chapter])) {
        // Create initial entry for this chapter
        $result[$section]['Chapters'][$chapter] = array(
            'ChapterNumber' => $chapter,
            'ChapterTitle' => $record['ChapterTitle'],
            'Pages' => array());
    }
    // Add record for this page
    $result[$section]['Chapters'][$chapter]['Pages'][] = array(
        'PageID' => $record['PageID'],
        'PageText' => $record['PageText'],
        'PageNumber' => $record['PageNumber'],
        'PageIsFree' => $record['PageIsFree'],
        'PageAudioUrl' => $record['PageAudioUrl']));
}

The result of this will be an associative array rather than index. If you need it to be indexed, you can use array_values():

$result = array_values($result);
foreach ($result as &$section) {
    $section['Chapters'] = array_values($section['Chapters'];
}
unset($section); // Break the reference variable link

Don't forget the & in the foreach, which makes $section a reference, so that assigning to this affects the array you're iterating over.

Ok got it. Thank you. This is helpful and I got it to work for my use case with some minor editing. TBH I started my approach similar to this using if statements to see what was already output or not, but was getting confused trying to compact the solution into just a few lines based on the solution to other question you marked as a duplicate of this. Should I edit this question, particularly the first paragraph, so it's more useful for the next user and remove all my commentary about the duplicate question/confusion? — Jeff Solomon, Oct 01 '18 at 19:56

Reformat PHP MYSQL Array Result Multiple Levels Deep

1 Answers1