Combining results from two tables into JSON data

29
April 15, 2019, at 11:50 PM

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   |
+----+---------+--------------+--------+

And the paragraph table is as below:

+----+--------------+-------------------+
| id | paragraph_no |     paragraph     |
+----+--------------+-------------------+
|  1 |       1      | hello how are you |
+----+--------------+-------------------+

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"
            }
        ]
    ]
}

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;

Which outputs only the words like this:

{
    "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...
    ]
}

How can I get my desired JSON output?

Answer 1

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];
}

Is your output for $data now what you need?

READ ALSO
How to query the unmodified timestamp inside a JSON object using MySQL?

How to query the unmodified timestamp inside a JSON object using MySQL?

I would like to query a JSON object containing a MySQL TIMESTAMP named 'time':

29
Multiple row INSERT from a single SELECT result

Multiple row INSERT from a single SELECT result

I am inserting multiple rows into a table, using the result from a subquery :

8
Equivalent Query from MySQL to PostgreSQL

Equivalent Query from MySQL to PostgreSQL

I'm switching from MySQL to PostgreSQL and trying to add a UNIQUE INDEX

16
MySQL / MariaDB column value unique in different column

MySQL / MariaDB column value unique in different column

I have a database schema for user login data that stores an E-Mail address for each userNaturally, this E-Mail needs to be unique because each user should only have one account

12