How to display data from the database in JSON format (using php)?

Question

PHP

Hello!
There are 3 tables categories, levels, locations. The locations table has relationships to the categories and levels tables.
categories_key table
title_c
1 name1
2 name2
3 name3
...
n levels_key
table
title_i map_i
1 level1 map1
2 level2 map2
...
n
locations_key table
title_l about_l key_c key_i
1 name1 about1 1 1
2 name2 about2 1 1
3 name3 about3 2 2
4 name4 about4 3 2
5 name5 about5 2 1
...
n
It is necessary to implement the output of the json array using php, its visual appearance is as follows:

{
    "mapwidth": "1000",
    "mapheight": "600",
    "categories": [
        { 
            "title": "name1"
        },
        {
            "title": "name2"
        },
        {
            "title": "name3"
        }
    ],
    "levels": [
        {
            "title": "level1",
            "map": "map1",
            "locations": [
                {
                    "title": "name1",
                    "about": "about1",
                    "categories": "name1"
                },
                {
                    "title": "name2",
                    "about": "about2",
                    "categories": "name1"
                },
                {
                    "title": "name5",
                    "about": "about5",
                    "categories": "name2"
                }
            ]
        },
        {
            "title": "level2",
            "map": "map2",
            "locations": [
                {
                    "title": "name3",
                    "about": "about3",
                    "categories": "name2"
                },
                {
                    "title": "name4",
                    "about": "about4",
                    "categories": "name3"
                }
            ]
        }
    ]
}

It currently outputs the following:

{
    "mapwidth": "1000",
    "mapheight": "600",
    "categories": [
        { 
            "title": "name1"
        },
        {
            "title": "name2"
        },
        {
            "title": "name3"
        }
    ],
    "levels": [
        {
            "title": "level1",
            "map": "map1",
            "locations": [
                {
                    "title": "name1",
                    "about": "about1",
                    "categories": "name1"
                },
                {
                    "title": "name2",
                    "about": "about2",
                    "categories": "name1"
                },
                {
                    "title": "name3",
                    "about": "about3",
                    "categories": "name2"
                },
                {
                    "title": "name4",
                    "about": "about4",
                    "categories": "name3"
                },
                {
                    "title": "name5",
                    "about": "about5",
                    "categories": "name2"
                }
            ]
        },
        {
            "title": "level2",
            "map": "map2",
            "locations": [
                {
                    "title": "name1",
                    "about": "about1",
                    "categories": "name1"
                },
                {
                    "title": "name2",
                    "about": "about2",
                    "categories": "name1"
                },
                {
                    "title": "name3",
                    "about": "about3",
                    "categories": "name2"
                },
                {
                    "title": "name4",
                    "about": "about4",
                    "categories": "name3"
                },
                {
                    "title": "name5",
                    "about": "about5",
                    "categories": "name2"
                }
            ]
        }
    ]
}

The PHP code at the moment is the following:

<?php
$link = mysqli_connect('localhost', '', '', 'base') or die('Could not connect: ' . mysqli_error());
    $query = "SELECT * FROM `categories`";
  $query1 = "SELECT * FROM `locations` l INNER JOIN `categories` c ON l.category_key=c.key_c WHERE 1";
  $query2 = "SELECT * FROM `levels`";
    $result = mysqli_query($link, $query) or die('Query failed: ' . mysqli_error());
  $result1 = mysqli_query($link, $query1) or die('Query failed: ' . mysqli_error());
  $result2 = mysqli_query($link, $query2) or die('Query failed: ' . mysqli_error());
  
$response = [
  'mapwidth'=>1000,
  'mapheight'=>600,
    'categories'=>[],
  'levels'=>[]
];

  while ($row = mysqli_fetch_object($result)) {
    $response['categories'][] = [
        'title'=>$row->title_c,
    ];
}

while ($row1 = mysqli_fetch_object($result1)) {
$response1[] = [
        'title'=>$row1->title_l,
        'about'=>$row1->about_l,
        'category'=>$row1->title_c
];

}
  while ($row2 = mysqli_fetch_object($result2)) {
    $response['levels'][] = [
        'title'=>$row1->title_i,
    'map'=>$row1->map_i,
    'locations'=>$response1
  ];
}

file_put_contents("chart_data.json", json_encode($response, JSON_UNESCAPED_UNICODE));
echo json_encode($response, JSON_UNESCAPED_UNICODE);
?>

Please help make this happen.

Answer 1

$link = mysqli_connect('localhost', '', '',
    'test') or die('Could not connect: '.mysqli_error());
$query = "SELECT * FROM `categories`";
$query1 = "SELECT * FROM `locations` l INNER JOIN `categories` c ON c.category_key=l.key_i WHERE 1";
$query2 = "SELECT * FROM `levels`";
$result = mysqli_query($link, $query) or die('Query failed: '.mysqli_error());
$result1 = mysqli_query($link, $query1) or die('Query failed: '.mysqli_error());
$result2 = mysqli_query($link, $query2) or die('Query failed: '.mysqli_error());

$response = [
    'mapwidth' => 1000,
    'mapheight' => 600,
    'categories' => [],
    'levels' => [],
];

while ($category = mysqli_fetch_object($result)) {
    $response['categories'][] = [
        'title' => $category->title_c,
    ];
}

while ($level = mysqli_fetch_object($result2)) {
    $response['levels'][$level->levels_key] = [
        'title' => $level->title_i,
        'map' => $level->map_i,
    ];
}

var_dump($result1);

while ($location = mysqli_fetch_object($result1)) {
    echo $location->key_i;
    $response['levels'][$location->key_i]['locations'][] = [
        'title' => $location->title_l,
        'about' => $location->about_l,
        'category' => $location->title_c,
    ];
}

file_put_contents("chart_data.json", json_encode($response, JSON_UNESCAPED_UNICODE));

echo json_encode($response, JSON_PRETTY_PRINT);