将两个表的结果合并到JSON中
我之前曾问过类似的问题，但没有book_no在该问题中包括该列，并且由于我想要的解决方案必须包括此列才能为我提供正确的JSON结构，因此我得到的解决方案因此无法正常工作。因此，我将使用适当的信息重新发布此信息。
我有两个表，words和paragraph。下words表如下：
+----+---------+--------------+---------+---------+
| id | book_no | paragraph_no | word_no | word |
+----+---------+--------------+---------+---------+
| 1 | 1 | 1 | 1 | hello |
+----+---------+--------------+---------+---------+
| 2 | 1 | 1 | 2 | how |
+----+---------+--------------+---------+---------+
| 3 | 1 | 1 | 3 | are |
+----+---------+--------------+---------+---------+
| 4 | 1 | 1 | 4 | you |
+----+---------+--------------+---------+---------+
下paragraph表如下：
+----+---------+--------------+-------------------+
| id | book_no | paragraph_no | paragraph |
+----+---------+--------------+-------------------+
| 1 | 1 | 1 | hello how are you |
+----+---------+--------------+-------------------+
我希望words表WHERE中的所有列book_no都是1，而段落表中具有相同WHERE子句的段落列都在一个JSON结果中。像这样的东西：
{
"1": [ <-- this is paragraph_no
"words": [
{
"id": "1",
"word_no": "1",
"paragraph_no": "1",
"word": "hello"
},
{
"id": "2",
"word_no": "2",
"paragraph_no": "1",
"word": "how"
},
// and so on...
],
"paragraph": [
{
"paragraph": "hello how are you"
}
]
]
}
仅输出这样的单词：
{
"1": [ <-- this is paragraph_no
{
"id": "1",
"word_no": "1",
"paragraph_no": "1",
"word": "hello"
},
{
"id": "2",
"word_no": "2",
"paragraph_no": "1",
"word": "how"
},
// and so on...
]
}
如何获得所需的JSON输出？
冉冉说1回答
-
拉丁的传说
这段代码会产生您需要的输出吗？$result_w = $conn->query("SELECT * FROM words;");$results_w = $result_w->fetch_all(MYSQLI_ASSOC);$words_per_paragraph = [];foreach($results_w as $key => $row) { $words_per_paragraph[$row['paragraph_no']][] = $row;}$result_p = $conn->query("SELECT * FROM paragraph;");$results_p = $result_p->fetch_all(MYSQLI_ASSOC);$data = [];foreach($results_p as $key => $row) { $p_no = $row['paragraph_no']; $words = []; if(array_key_exists($p_no, $words_per_paragraph)) { $words = $words_per_paragraph[$p_no]; } $data[$p_no] = [ 'words' => $words, 'paragraph' => $row ];}的内容$data（出于测试目的，我没有在第2段中添加任何文字）：{ "1":{ "words":{ "id":"4", "book_no":"1", "paragraph_no":"1", "word_no":"4", "word":"you" }, "paragraph":{ "id":"1", "book_no":"1", "paragraph_no":"1", "paragraph":"hello how are you" } }, "2":{ "words":[ ], "paragraph":{ "id":"3", "book_no":"1", "paragraph_no":"2", "paragraph":"I'm fine and you?" } }}也许您可以更改数据库表结构以在一条语句中获得所有内容。
随时随地看视频慕课网APP
PHP