MySQL到嵌套JSON

Question

This is almost exactly what I want，但是这个问题还没有得到回答，已经过去一年了。我想我已经接近了，但是数字被打印成了钥匙。在我的示例中，它显示在第47行，但对于实际文件中的每个"course_name“，它都是重复的。

[
 {
  "school_name": "Projects",
  "terms": [
    {
      "term_name":"category_name#1",
      "departments": [
        {
          "department_name":"sub_category_name1",
          "department_code":"category code text here",
          "courses":[
            {
              "course_name": "project1",
              "course_code":"project 1 code text goes here",
              "sections":[
                {
                  "section_code":"mike", 
                  "unique_id":"xxx@mail.com"
                },
                {
                  "section_code":"dan", 
                  "unique_id":"xxx@gmail.com"
                }
              ]
            },
            {
              "course_name": "project2",
              "course_code":"project 2 code text goes here",
              "sections":[
                {
                  "section_code":"steve", 
                  "unique_id":"xxx@mail.com"
                },
                {
                  "section_code":"chris", 
                  "unique_id":"xxx@gmail.com"
                }
              ]                       
            }
          ]   
        },
        {
          "department_name": "sub_category_name2",
          "department_code":"sub category description text goes here..",
          "courses": {
-->>>       "69": {
                "course_name": "project3",
                "course_code":"project3 code text goes here ",
                "sections":[
                  {
                    "section_code":"Alex", 
                    "unique_id":"xxx@gmail.com"
                  }                        
                ]                   
              }
             }
            }
          ]
        }
      ]
    }
  ]

下面是我正在使用的查询和返回数据的示例。

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

"UCA-2" "SPRING 2013" "ACCOUNTING" "ACCT" "3315" "COST ACCOUNTING" "10258" 10311

我所拥有的是用这段代码生成的。

$row_array = array();
$terms = array();
$departments = array();
$courses = array();

$h = 0;
$i = 0;
$j = 0;

while ($row = mysqli_fetch_assoc($fetch)) {
  $row_array[$row['school_name']]['school_name'] = $row['school_name'];

  $akey = array_search($row['term_name'], $terms);
  if ($akey === FALSE) {
    $m = $h++;
    $terms[] = $row['term_name'];
    $row_array[$row['school_name']]['terms'][$m]['term_name'] = $row['term_name'];
  } else {
    $m = $akey;
  }

  $key = array_search($row['department_code'], $departments);
  if ($key === FALSE) {
    $k = $i++;
    $departments[] = $row['department_code'];
    $row_array[$row['school_name']]['terms'][$m]['departments'][$k]['department_name'] = $row['department_name'];
    $row_array[$row['school_name']]['terms'][$m]['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'][$m]['departments'][$k]['courses'][$l]['course_name'] = $row['course_name'];
    $row_array[$row['school_name']]['terms'][$m]['departments'][$k]['courses'][$l]['course_code'] = $row['course_code'];
  } else {
    $l = $skey;
  }

  $row_array[$row['school_name']]['terms'][$m]['departments'][$k]['courses'][$l]['sections'][] = array('section_code' => $row['section_code'], 'unique_id' => $row['magento_course_id']);
}

如何在不显示这些数字的情况下生成此JSON？

Answer 1

我认为你有一些键和编码的问题。太多的键使用，代码中有过多的循环。也许你应该整理一下你的sql查询。

由于您正在为课程设置关键字，因此JSON会显示它。

尝试删除最后一行中"'courses'“之后的关键字，例如；

Change  ['courses'][$l] to  ['courses'][]

最后，为JSON编码数组。

 $result = json_encode($result);