将两个表的结果合并为JSON数据 [英] Combining results from two tables into JSON data

问题描述

我有两个表，words和paragraph. words表如下:

I have two tables, words and paragraph. The words table is as below:

+----+---------+--------------+--------+
| 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结果中.像这样:

I want all the columns from the words table WHERE paragraph_no is 1 and the paragraph column from paragraph table with the same WHERE clause all in one JSON results. Something like this:

{
    "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代码是:

Please excuse my mock-up but I need something similar to that. My current PHP code to only get the words are:

$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输出?

How can I get my desired JSON output?

推荐答案

为了获得所需的结构，您需要执行两个SQL语句-一个用于获取所有单词，一个用于获取所有段落.

In order to get your desired structure you need to execute two SQL statements - one for getting all words and one for getting all paragraphs.

$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的输出吗?

Is your output for $data now what you need?

这篇关于将两个表的结果合并为JSON数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！