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

问题描述

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

ALBUM (ID(PK),ALBUM_NAME,ARTIST_ID(FK),GENRES_ID(FK),ALBUM_POSTER)
SONG (ID(PK),SONG_NAME,ALBUM_ID(FK),ARTIST_ID(FK),SONG_LOCATION)
ARTIST (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"
        }
    ]
}
]

标签： phpmysqlsqljson

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

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

您只需根据当前专辑选择 SONG 记录...

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

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

问题描述

解决方案

推荐阅读