MySQL to nested JSON

Question

这几乎正是我想要的，但是这个问题还没有得到回答，而且已经一年了。我想我已经接近了，但是数字被当作钥匙打印出来了。在我的例子中，它出现在第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":"[e-mail]"
                },
                {
                  "section_code":"dan", 
                  "unique_id":"[e-mail]"
                }
              ]
            },
            {
              "course_name": "project2",
              "course_code":"project 2 code text goes here",
              "sections":[
                {
                  "section_code":"steve", 
                  "unique_id":"[e-mail]"
                },
                {
                  "section_code":"chris", 
                  "unique_id":"[e-mail]"
                }
              ]                       
            }
          ]   
        },
        {
          "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":"[e-mail]"
                  }                        
                ]                   
              }
             }
            }
          ]
        }
      ]
    }
  ]

这是我正在使用的查询和数据返回的一个例子。

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