我正在尝试通过MySQL和PHP构建以下JSON。
每个章节都有一个ID,一个章节标题和一个主题列表。
每个主题都有一个ID和一个主题区域
{
"Chapters": [
{
"id": "1",
"chapterTitle": "Introduction",
"Topics": [
{
"id": "1",
"topicArea": "C++"
},
{
"id": "2",
"topicArea": "Java"
}
]
},
{
"id": "2",
"chapterTitle": "Getting Started",
"Topics": [
{
"id": "1",
"topicArea": "Start Here"
}
]
}
]
}
但是,如果尝试以下PHP代码(1),则无法生成此输出。
//Get all chapters
$result = mysql_query("SELECT * FROM chapters");
while ($row = mysql_fetch_array($result))
{
$json[] = $row;
$json2 = array();
$chapterid = $row["id"];
//Fetch all topics within the first book chapter
$fetch = mysql_query("SELECT id,topicArea FROM topics where chapterid=$chapterid");
while ($row2 = mysql_fetch_assoc($fetch))
{
$json2[] = array(
'id' => $row2["id"],
'topicArea' => $row2["topicArea"]
);
}
$json['Topics'] = $json2; //I think the problem is here!
}
echo json_encode($json);
请不要再使用这些mysql_*
功能。双方mysqli
并PDO
允许您使用准备好的语句。
话虽如此,您说的$json['Topics'] = $json2;
就是问题所在:这必须是$json['Topics'][] = $json2;
。
最后,出于性能方面的原因(请参阅什么是SELECT N + 1?),您可能需要查看JOIN
。总而言之:
$dbh = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
$query = 'SELECT c.id AS chapter_id, c.chapterTitle, t.id as topic_id, t.topicArea FROM chapters c INNER JOIN topics t ON c.id = t.chapterid';
$json = array();
foreach ($dbh->query($query) as $row) {
$json[$row['chapter_id']]['id'] = $row->chapter_id;
$json[$row['chapter_id']]['chapter_title'] = $row->chapter_title;
$json[$row['chapter_id']]['Topics'][] = array(
'id' => $row->topic_id,
'topicArea' => $row->topicArea,
);
}
print json_encode(array_values($json));
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句