这是我的表结构。
我试图将MySQL转换为嵌套JSON，但我有麻烦弄清楚如何在PHP中构建多维数组。
我想要的结果类似于:
[
{
"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"
}
]
}
]
}
]
}
]
}
]
我正在使用的PHP:
$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));
我的JSON是这样的:
[
{
"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"
},
...
每个课程的院系信息都是重复的，使得文件更大。我正在寻找更好地理解PHP多维数组如何与JSON结合工作，因为我显然不知道。
我从Ian Mustafa的回复开始，我想出了解决每个循环擦除前一个数组的问题。
这是一个古老的线程，但我认为这可能对其他人有用，所以这是我的解决方案，但基于我自己的数据结构(很容易弄清楚如何适应其他结构，我认为):
$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;
结果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"
}
]
}
]
将while更改为:
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']
);
}
编辑
如果你想达到类似示例的结果，也许你应该考虑使用这个方法:
<?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));
也许这不是一个有效的程序，但它应该给你最好的结果。希望能有所帮助:)
试着用下面的代码替换你的while循环:
$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']);
}
我知道这是一个古老的问题，但今天我遇到了同样的问题。我没有在网上找到一个合适的解决方案，最后我解决了，所以我在这里发布，以便其他人可以检查。
我不是100%肯定这将工作，因为我没有你的DB，但在我的情况下是类似的和工作。它也不会是100%像它被要求，但我很确定不会有冗余，所有的数据将被显示。
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'];
}