php - 如何使用 PHP 和 SQL 生成嵌套的 JSON 响应

Question

我有四个表，ALBUM,ARTIST,SONG,GENRES

专辑(ID(PK)、ALBUM_NAME、ARTIST_ID(FK)、GENRES_ID(FK)、ALBUM_POSTER)
歌曲(ID(PK)、SONG_NAME、ALBUM_ID(FK)、ARTIST_ID(FK)、SONG_LOCATION)
艺术家(ID(PK),ARTIST_NAME)
GENRES(ID(PK)，GENRES_NAME)

我已经创建了波纹管 php 文件以创建 json 文件，如“所需输出”所示，但我得到了如下所示的“实际输出”。所有专辑中的所有歌曲都包含在每个专辑的“song_detail”部分中，如实际输出所示。

php

$sql = "SELECT DISTINCT ALBUM_ID, ALBUM_NAME,ARTIST_NAME,GENRES_NAME,ALBUM_POSTER "
    . "FROM ALBUM,ARTIST,SONG,GENRES "
    . "WHERE ALBUM.ID=SONG.ALBUM_ID AND SONG.ARTIST_ID=ARTIST.ID AND GENRES.ID=ALBUM.GENRES_ID";
$result = $db->query($sql);
$response["albums"] = array();

if ($result->num_rows > 0) 
{   
    while ($row = $result->fetch_assoc()) 
    {   $album = array();  
        $album["album_id"] = $row["ALBUM_ID"];
        $album["album_name"] = $row["ALBUM_NAME"];
        $album["artist_name"] = $row["ARTIST_NAME"];
        $album["genres_name"] = $row["GENRES_NAME"];
        $album["album_poster"] = "http://www.".$row["ALBUM_POSTER"];
        $album['song_details'] = array();

        $sql2 = "SELECT*"
        . " FROM SONG,ALBUM where SONG.ALBUM_ID = ALBUM.ID";
        $result2 = $db->query($sql2);
        while ($row2 = $result2->fetch_assoc())
        {       
            $album['song_details'][] = array(
            'songName' => $row2["SONG_NAME"],
            'song_location' => "http://www.".$row2["SONG_LOCATION"]);
        }
        array_push($response["albums"], $album);
    }
    $response["success"] = 1;
} 

期望的输出

[
{
    "album_name": "Startboy",
    "artist_name": "Weeknd",
    "genres_name":"R&B",
    "album_poster": "www.yyy.storage.x.png",
    "songs_details": [{
            "title": "False Alarm",
            "song_location": "www././../yw.mp3"
        },
        {
            "title": "Reminder",
            "song_location": "www././../x.mp3"
        }

    ]
}, 
{
    "album_name": "25",
    "artist_name": "Adele",
    "genres_name":"",
    "album_poster": "www.yyy.storage.a.png",
    "songs_details": [{
            "title": "Hello",
            "song_location": "www././../yy.mp3"
        },
        {
            "title": "I Miss You",
            "song_location": "www././../yx.mp3"
        }
    ]
}
]

实际输出

[
{
    "album_name": "Startboy",
    "artist_name": "Weeknd",
    "genres_name":"R&B",
    "album_poster": "www.yyy.storage.x.png",
    "songs_details": [{
            "title": "False Alarm",
            "song_location": "www././../yw.mp3"
        },
        {
            "title": "Reminder",
            "song_location": "www././../x.mp3"
        },
        {
            "title": "Hello",
            "song_location": "www././../yy.mp3"
        },
        {
            "title": "I Miss You",
            "song_location": "www././../yx.mp3"
    }]
}, 
{
    "album_name": "25",
    "artist_name": "Adele",
    "genres_name":"",
    "album_poster": "www.yyy.storage.a.png",
    "songs_details": [{
            "title": "False Alarm",
            "song_location": "www././../y.mp3"
        },
        {
            "title": "Reminder",
            "song_location": "www././../x.mp3"
        },
        {
            "title": "Hello",
            "song_location": "www././../yy.mp3"
        },
        {
            "title": "I Miss You",
            "song_location": "www././../yx.mp3"
        }
    ]
}
]

Answer 1

当您选择歌曲时，您的 SQL 不会限制轨道来自哪个专辑(尽管它确实确保它们与 ALBUM 相匹配)...

$sql2 = "SELECT*"
. " FROM SONG,ALBUM where SONG.ALBUM_ID = ALBUM.ID";

你只需要根据当前专辑选择歌曲记录...

$sql2 = "SELECT * FROM SONG where ID =".$row["ALBUM_ID"];

您可以在此处使用准备好的语句，但由于这是使用另一个表中的字段，因此可以使用它 - 但不是必需的。