将两个表的结果合并到JSON中 [英] Combining results from two tables into JSON

问题描述

我之前问过类似的问题，但是没有不要在问题中包含列book_no，并且由于我想要的解决方案必须包括此列才能为我提供正确的JSON结构，因此我得到的解决方案因此无法正常工作.因此，我将使用适当的信息重新发布.

I asked a similar question before but didn't include the column book_no in the question and since the solution that I wanted had to include this column to give me the proper JSON structure, the solution that I got didn't work because of this. Hence I'm re-posting this with the proper information.

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

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

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

I want all the columns from the words table WHERE book_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 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"
            }

        ]

    ]
}

请原谅我的模型，但我需要类似的东西.我当前仅能得到这些单词的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 book_no = 1");

$data = array();

while ($row = $result->fetch_assoc()) $data[$row['paragraph_no']][] = $row; // paragraph_no from 'words' table

$API_RESULT = json_encode($data, JSON_UNESCAPED_UNICODE);

echo $API_RESULT;

仅输出以下单词:

{
    "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输出?

How can I get my desired JSON output?

推荐答案

此代码是否产生所需的输出?

Does this code produce the output you need?

$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段中添加任何文字):

Content of $data (I haven't added any words to paragraph 2 for testing purposes):

{
   "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?"
      }
   }
}

也许您可以更改数据库表结构以在一条语句中获得所有内容.

Maybe you can change your database table structure to get everything in one statement.

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