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.

Community · Accepted Answer · 2017-05-23 12:31:35Z

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"
        }
    ]
}
]

Ian Mustafa · Accepted Answer · 2014-07-02 16:39:55Z

1

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 :)

edited Jul 2, 2014 at 16:39

answered Jul 2, 2014 at 16:09

Ian Mustafa

6185 silver badges19 bronze badges

4 Comments

Ben Pearson Over a year ago

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.

Ian Mustafa Over a year ago

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 Over a year ago

@user3798310 I added another method. Maybe you want to give it a try :)

Ben Pearson Over a year ago

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.

v2solutions.com · Accepted Answer · 2014-07-03 14:15:56Z

1

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.

answered Jul 3, 2014 at 14:15

v2solutions.com

1,4399 silver badges8 bronze badges

3 Comments

Ben Pearson Over a year ago

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.

v2solutions.com Over a year ago

Could you please share your table structures with sample data?

Ben Pearson Over a year ago

First, sorry it took all weekend. Second, I'm not sure what the preferred way to do this is, so here's a link docs.google.com/spreadsheets/d/…

Manos Nikolaidis · Accepted Answer · 2015-10-16 14:26:16Z

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.

Collectives™ on Stack Overflow

Using PHP multidimensional arrays to convert MySQL to JSON

4 Answers 4

1 Comment

Edit

4 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Edit

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related