我正在为一棵树创建分层JSON。 例如：
{
"name": "MD",
"children": [{
"name": "Professional",
"children": [{
"name": "Professional Behavours",
"children": [{
"name": "Year 1",
"children": [{
"name": "Integrated Medical Sciences 1"
}, {
"name": "Integrated Medical Sciences 2"
}]
}, {
"name": "Year 2",
"children": [{
"name": "Integrated Medical Practice 1",
"children": [{
"name": "Lecture - CVS"
}, {
"name": "Lecture - Type 1 Diabetes"
}]
}]
}, {...
这遵循层次结构：程序 -> 主题 -> 链 -> 年 -> 单元 ->学习事件。
除了 JSON 中的名称字段外，我现在还想添加一个"类型"字段，例如：
{
"name": "MD",
"type": "program",
"children": [{
"name": "Professional",
"type": "theme",
"children": [{
"name": "Professional Behavours",
"type": "strand",
"children": [{
"name": "Year 1",
"type": "strandyear",
"children": [{
"name": "Integrated Medical Sciences 1",
"type": "unit"
}, {
"name": "Integrated Medical Sciences 2",
"type": "unit"
}]
}, {
"name": "Year 2",
"type": "strandyear",
"children": [{
"name": "Integrated Medical Practice 1",
"type": "unit",
"children": [{
"name": "Lecture - CVS",
"type": "learning_event"
}, {
"name": "Lecture - Type 1 Diabetes",
"type": "learning_event"
}]
}]
}, {...
我正在使用以下MySQL和PHP为JSON创建数组：
$query = "SELECT CONCAT('program:', program_pk) AS global_id,
program_name AS name,
NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
theme_name AS name,
CONCAT('program:', program_fk) AS parent_global_id
FROM theme
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name) AS global_id,
strand_name AS name,
CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS global_id,
strandyear_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strandyear sy
INNER JOIN strand s ON s.strand_pk = sy.strand_fk
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS global_id,
unit_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS parent_global_id
FROM unit u
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name, ',learning_event:', learning_event_name) AS global_id,
learning_event_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS parent_global_id
FROM learning_event le
INNER JOIN unit u ON u.unit_pk = le.unit_fk
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk";
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
$data[$row->global_id] = $row;
}
$roots = array();
foreach ($data as $row) {
if ($row->parent_global_id === null) {
$roots[]= $row;
} else {
$data[$row->parent_global_id]->children[] = $row;
}
unset($row->parent_global_id);
unset($row->global_id);
}
$json = json_encode($roots);
您可以看到查询的数据库小提琴
鉴于数组确实包含类型的数据，我应该如何修改 PHP 和 SQL(如有必要(以在 JSON 中包含"类型"字段？
全阵列数据
...
foreach ($data as $row) {
$row->type = explode ( ':', $row->global_id)[0];
if ($row->parent_global_id === null) {
。
如果你可以改变 SQL - 那就更好了。
只需摆脱CONCAT(并在单独的字段中传递数据，允许 PHP 在需要时进行连接。在这种情况下，您将需要重写PHP和SQL。更多的工作，更少的收入。通过编程赚钱并不容易。