如何从多个类似的行mysql结果创建一个复杂的结构化json

Question

One festival has many stages. Multiple artists play at one stage.

Here is my Mysql result :

With that sql request, I'd like to create a json object with the following structure in PHP :

{
    "name": "mainstage",
    "artists": [
        {
            "name": "Gregori Klosman"
        },
        {
            "name": "Tristan Garner"
        }, 
        {
            "name": "Glow In The Dark"
        }, 
        {
            "name": "Jaxx Da Fishworks"
        }]
}, 
{
    "name": "Dim Mak",
    "artists" : [
        {
            "name": "Albin Myers"
        }
     ]
}

I tried to do that with the following script but the structure is slightly different as I have an ID to identify the stage.

$stages = array();
while($result_stages = $query_stages -> fetch()) {
    $artist = array("name" => $result_stages["artist_name"]);
    array_push($stages[$result_stages["stage_id"]]["artists"], $artist);
}

echo json_encode($stages);

Any ideas ? I could make it with multiple mysql requests and php loops but I feel this is not the most proper way to do that. Thanks for your answer!

Answer 1

My have tried like this ...

$sql="select stage_name from [table_name] group by stage_name";
$result=mysql_query($sql);
$arr = array();
$i=0;
while($row=mysql_fetch_array($result))
{
    $arr[$i]['name']=$row[0];
    $sql2="select artist_name from [table_name] where stage_name='".$row[0]."'";
    $result2=mysql_query($sql2);
    $arr2=array();
    while($row2=mysql_fetch_array($result2))
    {
      array_push($arr2,$row2[0]);
    }
 $arr[$i]['artist'] = $arr2;
 $i++;
}
 echo json_encode($arr);