php - PHP到JSON组数组

我的目标是列表歌手和点击歌手，以显示歌手的歌曲。
这是我的PHP

<?php
    $server = "localhost";
    $username = "user";
    $password = "123456";
    $database = "database";
    $con = mysql_connect($server, $username, $password) or die ("Could not connect: " . mysql_error());
    mysql_select_db($database, $con);
    mysql_query("set names 'utf8'");
    $sql = "SELECT a.singername,b.songname,b.songembeded from singers a,songs b where a.singerid=b.singerid";
    $result = mysql_query($sql) or die ("Query error: " . mysql_error());
    $records = array();
    while($row = mysql_fetch_assoc($result)) {
        $records[] = $row;
    }
    mysql_close($con);
    utf8_encode($records);
    echo json_encode($records);
?>

这是我的json输出：

[{"singername":"singerA", "songname":"songA", "songembeded":"https://www.youtube.com"},{"singername":"singerA", "songname":"songB", "songembeded":"https://www.youtube.com"}, "singername":"singerB","songname":"songX", "songembeded":"https://www.youtube.com"}]

我想像一群歌手和他们的歌曲一样：

["singername":"singerA","songs": {"songname":"songA","songembeded":"https://www.youtube.com"},{"songname":"songB","songembeded":"https://www.youtube.com"},
"singername":"singerB","songs": {"songname":"songX","songembeded":"https://www.youtube.com"}]

最佳答案

稍微更改一下查询：

$sql = "SELECT a.singername,b.songname,b.songembeded from singers a,songs b where a.singerid=b.singerid ORDER BY a.singername";

因此，请替换循环：

$records = array();
    while($row = mysql_fetch_assoc($result)) {
        $records[] = $row;
    }

与这个

$records = array();
$songs= array();
$singername='';
while($row = mysql_fetch_assoc($result)) {

    if ($singername!='' && $singername!=$row['singername']) {
       $records[]= array("singername"=>$singername,"songs"=>$songs);
       $songs = array();
    }
    $songs[] = array('songname'=>$row['songname'],'songembeded'=>$row['songembeded']);
    $singername=$row['singername'];
}
$records[]= array("singername"=>$singername,"songs"=>$songs);