将两个表的结果合并为JSON数据
我有两个表，words和paragraph。下words表如下：
+----+---------+--------------+--------+
| id | word_no | paragraph_no | word |
+----+---------+--------------+--------+
| 1 | 1 | 1 | hello |
+----+---------+--------------+--------+
| 2 | 2 | 1 | how |
+----+---------+--------------+--------+
| 3 | 3 | 1 | are |
+----+---------+--------------+--------+
| 4 | 4 | 1 | you |
+----+---------+--------------+--------+
下paragraph表如下：
+----+--------------+-------------------+
| id | paragraph_no | paragraph |
+----+--------------+-------------------+
| 1 | 1 | hello how are you |
+----+--------------+-------------------+
我希望words表WHERE中的所有列paragraph_no都是1，而段落表中具有相同WHERE子句的段落列都在一个JSON结果中。像这样的东西：
{
"1": [ <-- this is the paragraph number
"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"
}
]
]
}
请原谅我的模型，但我需要类似的东西。我当前仅能得到的单词的PHP代码是：
$result = $conn->query("SELECT * FROM words WHERE paragraph_no = 1");
$data = array();
while ($row = $result->fetch_assoc()) $data[$row['paragraph_no']][] = $row;
$API_RESULT = json_encode($data, JSON_UNESCAPED_UNICODE);
echo $API_RESULT;
仅输出这样的单词：
{
"1": [ <-- this is the paragraph number
{
"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回答
-
白猪掌柜的
为了获得所需的结构，您需要执行两个SQL语句-一个用于获取所有单词，另一个用于获取所有段落。$result_w = $conn->query("SELECT * FROM words;");$results_w = $result->fetch_all(MYSQLI_ASSOC);$result_p = $conn->query("SELECT * FROM paragraphs;");$results_p = $result->fetch_all(MYSQLI_ASSOC);$paragraphs = [];foreach($results_p as $key => $row) { $paragraphs[$row['id']] = $row;}$data = [];foreach($results_w as $key => $row) { $p_no = $row['paragraph_no']; $data[$p_no]['words'] = $results_w; $data[$p_no]['paragraph'] = $paragraphs[$p_no];}您$data现在的输出是您所需要的吗？
随时随地看视频慕课网APP
PHP