CODEWITHSUNDEEP
phpmysqljson
IlludiumPu36
IlludiumPu36

Reputation: 4304

Add field in hierarchical JSON

I am creating hierarchical JSON for a tree. E.g.:

{
    "name": "MD",
    "children": [{
        "name": "Professional",
        "children": [{
            "name": "Professional Behavours",
            "children": [{
                "name": "Year 1",
                "children": [{
                    "name": "Integrated Medical Sciences 1"
                }, {
                    "name": "Integrated Medical Sciences 2"
                }]
            }, {
                "name": "Year 2",
                "children": [{
                    "name": "Integrated Medical Practice 1",
                    "children": [{
                        "name": "Lecture - CVS"
                    }, {
                        "name": "Lecture - Type 1 Diabetes"
                    }]
                }]
            }, {...

This follows the heirachical structure: program -> theme -> strand -> year -> unit -> learning event.

In addition to the name field in the JSON, I now want to add a 'type' field, e.g.:

    {
        "name": "MD",
        "type": "program",
        "children": [{
            "name": "Professional",
            "type": "theme",
            "children": [{
                "name": "Professional Behavours",
                "type": "strand",
                "children": [{
                    "name": "Year 1",
                    "type": "strandyear",
                    "children": [{
                        "name": "Integrated Medical Sciences 1",
                        "type": "unit"
                    }, {
                        "name": "Integrated Medical Sciences 2",
                        "type": "unit"
                    }]
                }, {
                    "name": "Year 2",
                    "type": "strandyear",
                    "children": [{
                        "name": "Integrated Medical Practice 1",
                        "type": "unit",
                        "children": [{
                            "name": "Lecture - CVS",
                            "type": "learning_event"
                        }, {
                            "name": "Lecture - Type 1 Diabetes",
                            "type": "learning_event"
                        }]
                    }]
                }, {...

I am using the following MySQL and PHP to create the array for the JSON:

 $query = "SELECT CONCAT('program:', program_pk) AS global_id,
       program_name AS name,
       NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
       theme_name AS name,
       CONCAT('program:', program_fk) AS parent_global_id
FROM theme 
UNION ALL
SELECT 
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS global_id,
       strand_name AS name,
       CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT 
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS global_id,
       strandyear_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strandyear sy 
INNER JOIN strand s ON s.strand_pk = sy.strand_fk

UNION ALL
SELECT 
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS global_id,
       unit_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS parent_global_id
FROM unit u 
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk

UNION ALL
SELECT 
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name, ',learning_event:', learning_event_name) AS global_id,
       learning_event_name AS name,
       CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS parent_global_id
FROM learning_event le
INNER JOIN unit u ON u.unit_pk = le.unit_fk
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk";
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
    $data[$row->global_id] = $row;
}

$roots = array();
foreach ($data as $row) {   
    if ($row->parent_global_id === null) {
        $roots[]= $row;
    } else {
        $data[$row->parent_global_id]->children[] = $row;
    }
    unset($row->parent_global_id);
    unset($row->global_id);
}

$json = json_encode($roots);

You can see a db-fiddle of the query

How should I modify the PHP, and SQL if necessary, to include the 'type' field in the JSON, given that the array does include that data for type?

Full array data

Upvotes: 2

Views: 58

Answers (1)

Yevgeniy Afanasyev
Yevgeniy Afanasyev

Reputation: 41360

...

foreach ($data as $row) {
    $row->type = explode ( ':',  $row->global_id)[0];
    if ($row->parent_global_id === null) {

...

if you can change SQL - it is even better.

just get rid of CONCAT( and pass data in separate fields, allow PHP to do the concatination when needed. You will need to rewrite PHP and SQL in this case. More work, less income. It is not easy to earn money with programming.

Upvotes: 1

Related Questions