Как вывести данные из базы в формате JSON (с помощью php)?

Question

[MrZek]

Здравствуйте!
Есть 3 таблицы categories, levels, locations. У таблицы locations есть связи с таблицами categories и levels.

Таблица categories
category_key title_c
1 name1
2 name2
3 name3
...
n

Таблица levels
levels_key title_i map_i
1 level1 map1
2 level2 map2
...
n

Таблица locations
locations_key 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

Нужно с помощью php реализовать вывод json массива, визуальный его вид такой:

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

На данный момент выводится следующие:

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

Код PHP на данный момент следующий:

<?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);
?>

Пожалуйста помогите это реализовать.

Answer 1

[x404]

$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);

Comments

[MrZek]

На данный момент выводится следующие (кометы напротив того, что не соответствует эталону):

{ 
	"mapwidth": 1000, 
	"mapheight": 600, 
	"categories": [ 
			{ 
				"title": "name1" 
			}, 
			{ 
				"title": "name2" 
			}, 
			{ 
				"title": "name3" 
			} 
		], 
	"levels": 
	{   \\ Тут должна быть [
		"1":  \\ Не должно выводится
		{ 
			"title": "level1", 
			"map": "map1", 
			"locations": [ 
				{ 
					"title": "name1", 
					"about": "abou1", 
					"category": "name1" 
				}, 
				{ 
					"title": "name2", 
					"about": "about2", 
					"category": "name1" 
				}, 
				{ 
					"title": "name5", 
					"about": "about5", 
					"category": "name1" 
				} 
			]
		}, 
		"2": \\ Не должно выводится
		{ 
			"title": "level2", 
			"map": "map2", 
			"locations": [ 
				{ 
					"title": "name3", 
					"about": "about3", 
					"category": "name2" 
				}, 
				{ 
					"title": "name4", 
					"about": "about4", 
					"category": "name2" 
				} 
			] 
		} 
	} \\ Тут должна быть ]
}

Если в блоке

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

изменить
'levels' => [],

на

'levels' => [''],

то выводит следующие (кометы напротив того, что не соответствует эталону):

{ 
	"mapwidth": 1000, 
	"mapheight": 600, 
	"categories": [ 
			{ 
				"title": "name1" 
			}, 
			{ 
				"title": "name2" 
			}, 
			{ 
				"title": "name3" 
			} 
		], 
	"levels": 
	[ 
		"", \\ Не должно выводится
		{ 
			"title": "level1", 
			"map": "map1", 
			"locations": [ 
				{ 
					"title": "name1", 
					"about": "abou1", 
					"category": "name1" 
				}, 
				{ 
					"title": "name2", 
					"about": "about2", 
					"category": "name1" 
				}, 
				{ 
					"title": "name5", 
					"about": "about5", 
					"category": "name1" 
				} 
			]
		},  
		{ 
			"title": "level2", 
			"map": "map2", 
			"locations": [ 
				{ 
					"title": "name3", 
					"about": "about3", 
					"category": "name2" 
				}, 
				{ 
					"title": "name4", 
					"about": "about4", 
					"category": "name2" 
				} 
			] 
		} 
	] 
}

[MrZek]

Решение нашел непроходимо, было сбросить ключи массива.

$response["levels"] = array_values($response["levels"]);

перед

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