Using PHP multidimensional arrays to convert MySQL to JSON

Question

I'm trying to convert MySQL to nested JSON, but am having trouble figuring out how to build the multidimensional array in PHP.

The result I want is similar to this:

[
{
    "school_name": "School's Name",
    "terms": [
        {                                       
            "term_name":"FALL 2013",
            "departments": [
                {
                    "department_name":"MANAGEMENT INFO SYSTEMS",
                    "department_code":"MIS",
                    "courses": [
                        {
                            "course_code":"3343",
                            "course_name":"ADVANCED SPREADSHEET APPLICATIONS",
                            "sections": [
                                {
                                    "section_code":"18038",
                                    "unique_id": "mx00fdskljdsfkl"
                                },
                                {
                                    "section_code":"18037",
                                    "unique_id": "mxsajkldfk57"
                                }
                            ]
                        },
                        {
                            "course_code":"4370",
                            "course_name":"ADVANCED TOPICS IN INFORMATION SYSTEMS",
                            "sections": [
                                {
                                    "section_code":"18052",
                                    "unique_id": "mx0ljjklab57"
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
} 
]

The PHP I'm using:

$query = "SELECT school_name, term_name, department_name, department_code, course_code, course_name, section_code, magento_course_id
    FROM schools INNER JOIN term_names ON schools.id=term_names.school_id INNER JOIN departments ON schools.id=departments.school_id INNER JOIN adoptions ON departments.id=adoptions.department_id";

$fetch = mysqli_query($con, $query) or die(mysqli_error($con));
$row_array = array();
while ($row = mysqli_fetch_assoc($fetch)) {
  $row_array[$row['school_name']]['school_name'] = $row['school_name'];
  $row_array[$row['school_name']]['terms']['term_name'] = $row['term_name'];
  $row_array[$row['school_name']]['terms']['departments'][] = array(
    'department_name' => $row['department_name'],
    'department_code' => $row['department_code'],
    'course_name' => $row['course_name'],
    'course_code' => $row['course_code'],
    'section_code' => $row['section_code'],
    'unique_id' => $row['magento_course_id']
  );
}

$return_arr = array();
foreach ($row_array as $key => $record) {
  $return_arr[] = $record;
}

file_put_contents("data/iMadeJSON.json" , json_encode($return_arr, JSON_PRETTY_PRINT));

My JSON looks like this:

[
{
    "school_name": "School's Name",
    "terms": {
        "term_name": "FALL 2013",
        "departments": [
            {
                "department_name": "ACCOUNTING",
                "department_code": "ACCT",
                "course_name": "COST ACCOUNTING",
                "course_code": "3315",
                "section_code": "10258",
                "unique_id": "10311"
            },
            {
                "department_name": "ACCOUNTING",
                "department_code": "ACCT",
                "course_name": "ACCOUNTING INFORMATION SYSTEMS",
                "course_code": "3320",
                "section_code": "10277",
                "unique_id": "10314"
            },
            ...

The department information is repeated for each course, making the file much larger. I'm looking for a better understanding of how PHP multidimensional arrays in conjunction with JSON works, because I apparently have no idea.

score 4 · Answer 1 · edited May 23 '17 at 12:31

I started from Ian Mustafa reply and I figure out to solve the problem of each loop erasing the previous array.

It's an old thread but I think this could be useful to others so here is my solution, but based on my own data structure (easy to figure out how to adapt it to other structures I think) :

$usersList_array =array();
$user_array = array();
$note_array = array();

$fetch_users = mysqli_query($mysqli, "SELECT ID, Surname, Name FROM tb_Users WHERE Name LIKE 'G%' ORDER BY ID") or die(mysqli_error($mysqli));
while ($row_users = mysqli_fetch_assoc($fetch_users)) {
    $user_array['id'] = $row_users['ID'];
    $user_array['surnameName'] = $row_users['Surname'].' '.$row_users['Name'];
    $user_array['notes'] = array();

    $fetch_notes = mysqli_query($mysqli, "SELECT id, dateIns, type, content FROM tb_Notes WHERE fk_RefTable = 'tb_Users' AND fk_RefID = ".$row_users['ID']."") or die(mysqli_error($mysqli));
    while ($row_notes = mysqli_fetch_assoc($fetch_notes)) {
        $note_array['id']=$row_notes['id'];
        $note_array['dateIns']=$row_notes['dateIns'];
        $note_array['type']=$row_notes['type'];
        $note_array['content']=$row_notes['content'];
        array_push($user_array['notes'],$note_array);
    }

    array_push($usersList_array,$user_array);
}

$jsonData = json_encode($usersList_array, JSON_PRETTY_PRINT);


echo $jsonData;

Resulting JSON :

[
{
    "id": "1",
    "surnameName": "Xyz Giorgio",
    "notes": [
        {
            "id": "1",
            "dateIns": "2016-05-01 03:10:45",
            "type": "warning",
            "content": "warning test"
        },
        {
            "id": "2",
            "dateIns": "2016-05-18 20:51:32",
            "type": "error",
            "content": "error test"
        },
        {
            "id": "3",
            "dateIns": "2016-05-18 20:53:00",
            "type": "info",
            "content": "info test"
        }
    ]
},
{
    "id": "2",
    "cognomeNome": "Xyz Georg",
    "notes": [
        {
            "id": "4",
            "dateIns": "2016-05-20 14:38:20",
            "type": "warning",
            "content": "georg warning"
        },
        {
            "id": "5",
            "dateIns": "2016-05-20 14:38:20",
            "type": "info",
            "content": "georg info"
        }
    ]
}
]

Works like a gem and saved me a ton of time, thankyou! – luke May 25 '16 at 08:38 — luke, May 25 '16 at 08:38

Ian Mustafa · Answer 2 · 2014-07-02T16:39:55.873

Change your while to this:

while ($row = mysqli_fetch_assoc($fetch)) {
    $row_array[$row['school_name']]['school_name'] = $row['school_name'];
    $row_array[$row['school_name']]['terms']['term_name'] = $row['term_name'];
    $row_array[$row['school_name']]['terms']['department_name'][] = array(
        'department_name' => $row['department_name'],
        'department_code' => $row['department_code']
    );
}

Edit

If you want to achieve result like the example, maybe you should consider using this method:

<?php

$result_array = array();

$fetch_school = mysqli_query($con, "SELECT id, school_name FROM schools") or die(mysqli_error($con));
while ($row_school = mysqli_fetch_assoc($fetch_school)) {
    $result_array['school_name'] = $row_school['school_name'];

    $fetch_term = mysqli_query($con, "SELECT term_name FROM term_names WHERE school_id = $row_school['id']") or die(mysqli_error($con));
    while ($row_term = mysqli_fetch_assoc($fetch_term)) {
        $result_array['terms']['term_name'] = $row_term['term_name'];

        $fetch_dept = mysqli_query($con, "SELECT id, department_name, department_code FROM departments WHERE school_id = $row_school['id']") or die(mysqli_error($con));
        while ($row_dept = mysqli_fetch_assoc($fetch_dept)) {
            $result_array['terms']['deptartments']['department_name'] = $row_dept['department_name'];
            $result_array['terms']['deptartments']['department_code'] = $row_dept['department_code'];

            $fetch_course = mysqli_query($con, "SELECT course_name, course_code FROM adoptions WHERE departement_id = $row_dept['id']") or die(mysqli_error($con));
            while ($row_course = mysqli_fetch_assoc($fetch_course)) {
                $result_array['terms']['deptartments']['courses']['course_name'] = $row_course['course_name'];
                $result_array['terms']['deptartments']['courses']['course_code'] = $row_course['course_code'];
            }
        }
    }
}

file_put_contents("data/iMadeJSON.json" , json_encode($result_array, JSON_PRETTY_PRINT));

Probably it's not an effective program, but it should gives you best result. Hope it helps :)

That's not quite what I need. Your code causes the department name to be repeated for every course, but I want the courses to be nested inside their respective department. e: not in, but under, like in the JSON example at the beginning of my question. — Ben Pearson, Jul 02 '14 at 16:20
Then we need to modify your whole `SELECT` query, to select parent array first, and load child array afterwards. I'll edit my answer soon. — Ian Mustafa, Jul 02 '14 at 16:21
@user3798310 I added another method. Maybe you want to give it a try :) — Ian Mustafa, Jul 02 '14 at 16:42
It's definitely the right format. The only problem is that each loop erases the previous array, so only the last course of the last department is printed to the JSON. It seems like I'm way closer than I was though, so thank you. — Ben Pearson, Jul 02 '14 at 17:19

score 1 · Answer 3 · answered Jul 03 '14 at 14:15

Try replacing your while loop with below code:

$departments = array();
$courses = array();

$i = 0;
$j = 0;

while ($row = mysqli_fetch_assoc($fetch)) {
    $row_array[$row['school_name']]['school_name'] = $row['school_name'];
    $row_array[$row['school_name']]['terms']['term_name'] = $row['term_name'];

    $key = array_search($row['department_code'], $departments); 
    if ($key === FALSE) {        
        $k = $i++;
        $departments[] = $row['department_code'];
        $row_array[$row['school_name']]['terms']['departments'][$k]['department_name'] = $row['department_name'];
        $row_array[$row['school_name']]['terms']['departments'][$k]['department_code'] = $row['department_code'];
    } else {
        $k = $key;
    }   

    $skey = array_search($row['course_code'], $courses); 
    if ($skey === FALSE) {        
        $l = $j++;
        $courses[] = $row['course_code'];
        $row_array[$row['school_name']]['terms']['departments'][$k]['courses'][$l]['course_name'] = $row['course_name'];
        $row_array[$row['school_name']]['terms']['departments'][$k]['courses'][$l]['course_code'] = $row['course_code'];
    } else {
        $l = $skey;
    } 

    $row_array[$row['school_name']]['terms']['departments'][$k]['courses'][$l]['sections'][] = array('section_code' => $row['section_code'], 'unique_id' => $row['magento_course_id']);
}

Hope this would help you.

The first department comes out perfect, but the after that every course looks like this ... "department_name": "ART", "department_code": "ART", "courses": { "28": { "course_name": ... The "28" key is the problem. — Ben Pearson, Jul 03 '14 at 14:43
Could you please share your table structures with sample data? — v2solutions.com, Jul 04 '14 at 05:15
First, sorry it took all weekend. Second, I'm not sure what the preferred way to do this is, so here's a link https://docs.google.com/spreadsheets/d/1dMpnB3P62Bv1xEwUQtpZFNUH8iEsOER88QofIgzlR5k/edit?usp=sharing — Ben Pearson, Jul 07 '14 at 13:05

score 1 · Answer 4 · edited Oct 16 '15 at 14:26

I know that this is a kind of an old question, but today I was with the same issue. I didn't find a proper solution online and finally I solved, so I'm posting here so others can check.

I'm not 100% sure that this will work because I don't have your DB, but in my case was similar and worked. Also it will not be 100% like it was asked, but I'm pretty sure there will be no redundancy and all the data will be shown.

while ($row = mysqli_fetch_assoc($fetch)) {
$row_array ['school_name'][$row['school_name']]['terms'][$row['term_name']]['departments']['department_code'][$row['department_code']]['department_name'] = $row['department_name'];
$row_array ['school_name'][$row['school_name']]['terms'][$row['term_name']]['departments']['department_code'][$row['department_code']]['courses']['course_code'][$row['course_code']]['course_name'] = $row['course_name'];
$row_array ['school_name'][$row['school_name']]['terms'][$row['term_name']]['departments']['department_code'][$row['department_code']]['courses']['course_code'][$row['course_code']]['sections']['unique_id'][$row['magento_course_id']]['section_code'] = $row['section_code'];
}

Also I'm not a PHP guy, but from what I understand, the = comes before a leaf and only before a leaf.

Using PHP multidimensional arrays to convert MySQL to JSON

4 Answers4

Edit

Linked