Вот моя структура таблицы.
Я пытаюсь преобразовать 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 работают, потому что я, по-видимому, понятия не имею.
Попробуйте заменить цикл 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%, что это сработает, потому что у меня нет вашей БД, но в моем случае было похожее и сработало. Также он не будет на 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']; }
Кроме того, я не парень PHP, но из того, что я понимаю, = приходит перед листом и только перед листом.
Я начал с ответа Иана Мустафы, и я решил решить проблему каждого цикла, стирая предыдущий массив.
Это старая ветка, но я думаю, что это может быть полезно для других, так что вот мое решение, но на основе моей собственной структуры данных (легко понять, как адаптировать ее к другим структурам, я думаю):
$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));
Вероятно, это не эффективная программа, но она должна дать лучший результат. Надеюсь, поможет 🙂