在mysql中连接表以创建json的正确方法

rqqzpn5f 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(311)

我试图用正确的方式在php查询中使用mysql连接。我已经读了很多指南和答案，但我找不到一个真实的例子如何连接一个“父”表和它的所有“子”。
例如。我有三张table。其中一个叫“乐队”

+---------+-----------+-------------+
| band_id | band_name | band_rating |
+---------+-----------+-------------+
| 777     | Beatles   | 100         |
+---------+-----------+-------------+

第二张table叫音乐家。它的“band\u id”列与bands中的“band\u id”连接

+-------------+---------+----------------+---------------------+
| musician_id | band_id | musician_name  | musician_instrument |
+-------------+---------+----------------+---------------------+
| 1           | 777     | John Lennon    | Voice               |
+-------------+---------+----------------+---------------------+
| 2           | 777     | Paul McCartney | Guitar              |
+-------------+---------+----------------+---------------------+
| 3           | 777     | Ringo Starr    | Drums               |
+-------------+---------+----------------+---------------------+

最后一张table叫宋。它还有“band\u id”列，该列与band中的“band\u id”连接。

+---------+---------+-----------+-----------+
| song_id | band_id | song_name | song_year |
+---------+---------+-----------+-----------+
| 1       | 777     | Hey Jude  | 1968      |
+---------+---------+-----------+-----------+
| 2       | 777     | Let it be | 1970      |
+---------+---------+-----------+-----------+
| 3       | 777     | Yesterday | 1965      |
+---------+---------+-----------+-----------+

所以，我应该如何使用join进行myslq查询，以获得乐队的所有歌曲和音乐家。例如，我想将最终echo作为json：

{
"id": 777,
"name": "Beatles",
"rating": 100,
"musicians":
[
    {
    "id": 1,
    "name": "John Lennon",
    "instrument": "Voice"
    },    

    {
    "id": 2,
    "name": "Paul McCartney",
    "instrument": "Guitar"
    },

    {
    "id": 3,
    "name": "Ringo Starr",
    "instrument": "Drums"
    } 
],
"songs":
[
    {
    "id":1,
    "name": "Hey Jude",
    "year": "1968"
    },

    {
    "id":2,
    "name": "Let it be",
    "year": "1970"
    },

    {
    "id":3,
    "name": "Yesterday",
    "year": "1965"
    }
]
}

什么是进行此类查询的正确方法？

mysql php

来源：https://stackoverflow.com/questions/53899864/right-way-to-join-tables-in-mysql-for-creating-json

1条答案

按热度按时间

qcuzuvrc1#

您可以运行两个查询，然后解析这些查询的结果以获得结果。

$musicians = DB::select("SELECT bands.id, bands.band_id, bands.band_name, bands.band_rating, musicians.musician_id as musician_id, 
                              musicians.musician_name as musician_name, musicians.musician_instrument as musician_instrument
                            FROM bands
                            LEFT JOIN musicians
                            ON bands.band_id = musicians.band_id
                            Where bands.band_id=777;");

            $songs = DB::select("SELECT bands.id, bands.band_id, bands.band_name, bands.band_rating, Songs.song_id as song_id, Songs.song_name as song_name, 
                            Songs.song_year as song_year
                        FROM bands
                        LEFT JOIN Songs
                        ON bands.band_id = Songs.band_id
                        Where bands.band_id=777;");

            $output = array();
            $output['id'] = $musicians[0]->band_id;
            $output['name'] = $musicians[0]->band_name;
            $output['rating'] = $musicians[0]->band_rating;
            $output['musicians'] = array();
            $output['songs'] = array();

            $index = 0;
            foreach($musicians as $data) {
                $output['musicians'][$index]['id'] = $data->musician_id;
                $output['musicians'][$index]['name'] = $data->musician_name;
                $output['musicians'][$index]['instrument'] = $data->musician_instrument;
                $index++;
            }

            $index = 0;
            foreach($songs as $data) {
                $output['songs'][$index]['id'] = $data->song_id;
                $output['songs'][$index]['name'] = $data->song_name;
                $output['songs'][$index]['instrument'] = $data->song_year;
                $index++;
            }

最后，您可以使用json_encode（）函数获得json格式的输出。

$finalOutput = json_encode($output);

赞(0）回复(0）举报 2021-06-16

我来回答

在mysql中连接表以创建json的正确方法

1条答案

相关问题

热门标签

最新问答