使用 PHP 多维数组将 MySQL 转换为 JSON [英] Using PHP multidimensional arrays to convert MySQL to JSON
问题描述
我正在尝试将 MySQL 转换为嵌套的 JSON,但我无法弄清楚如何在 PHP 中构建多维数组.
我想要的结果是这样的:
<预><代码>[{"school_name": "学校名称",条款":[{"term_name":"2013 年秋季",部门":[{"department_name":"管理信息系统","部门代码":"管理信息系统",培训班": [{"课程代码":"3343","course_name":"高级电子表格应用程序",部分":[{"section_code":"18038","unique_id": "mx00fdskljdsfkl"},{"section_code":"18037",unique_id":mxsajkldfk57"}]},{"课程代码":"4370","course_name":"信息系统中的高级主题",部分":[{"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从学校 INNER JOIN term_names ON school.id=term_names.school_id INNER JOIN 部门 ON school.id=departments.school_id INNER JOIN ON Departments.id=adoptions.department_id";$fetch = mysqli_query($con, $query) 或 die(mysqli_error($con));$row_array = array();而 ($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('部门名称' =>$row['department_name'],'部门代码' =>$row['department_code'],'课程名称' =>$row['course_name'],'课程代码' =>$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": "学校名称",条款":{"term_name": "2013 年秋季",部门":[{"部门名称": "会计","department_code": "ACCT","course_name": "成本核算",课程代码":3315","section_code": "10258","unique_id": "10311"},{"部门名称": "会计","department_code": "ACCT","course_name": "会计信息系统",课程代码":3320","section_code": "10277",unique_id":10314"},...每门课程都重复部门信息,使文件变得更大.我正在寻找更好地理解 PHP 多维数组与 JSON 的工作原理,因为我显然不知道.
我从 Ian Mustafa 回复 开始,我想out 来解决每次循环擦除前一个数组的问题.
这是一个旧线程,但我认为这可能对其他人有用,所以这是我的解决方案,但基于我自己的数据结构(很容易弄清楚如何将其适应我认为的其他结构):
$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));而 ($row_users = mysqli_fetch_assoc($fetch_users)) {$user_array['id'] = $row_users['ID'];$user_array['surnameName'] = $row_users['Surname'].''.$row_users['姓名'];$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));而 ($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);回声 $jsonData;
结果 JSON :
<预><代码>[{"id": "1","surnameName": "Xyz Giorgio",注释":[{"id": "1","dateIns": "2016-05-01 03:10:45",类型":警告",内容":警告测试"},{"id": "2","dateIns": "2016-05-18 20:51:32",类型":错误",内容":错误测试"},{"id": "3","dateIns": "2016-05-18 20:53:00","类型": "信息",内容":信息测试"}]},{"id": "2","cognomeNome": "Xyz Georg",注释":[{"id": "4","dateIns": "2016-05-20 14:38:20",类型":警告",内容":乔治警告"},{"id": "5","dateIns": "2016-05-20 14:38:20","类型": "信息",内容":乔治信息"}]}]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.
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"
}
]
}
]
这篇关于使用 PHP 多维数组将 MySQL 转换为 JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!