Group multiple mysql result by one column value

Question

I'm trying to fetch all results form mysql table and convering to Json format. Each row may contain a common column value(movie_name). I want to group results by that common field.

this is my first result with this query

$slots = SELECT movie_name,id as slot_id,screen_id,time,price FROM `slots`;

Result:

{
        "movies": [
            {
                "movie_name": "iceage",
                "slot_id": "142",
                "screen_id": "45",
                "time": "6.00",
                "price": "204"
            },
            {
                "movie_name": "lights out",
                "slot_id": "146",
                "screen_id": "45",
                "time": "11.00",
                "price": "150"
            },
            {
                "movie_name": "lights out",
                "slot_id": "147",
                "screen_id": "45",
                "time": "2.00",
                "price": "103"
            },
            {
                "movie_name": "conjuring",
                "slot_id": "148",
                "screen_id": "45",
                "time": "9.00",
                "price": "500"
            },
            {
                "movie_name": "iceage",
                "slot_id": "151",
                "screen_id": "45",
                "time": "",
                "price": "11"
            },
            {
                "movie_name": "lights out",
                "slot_id": "155",
                "screen_id": "45",
                "time": "11",
                "price": "11"
            }
        ]
}

i want to change above result like this:-

{
    "movies": [
            {
                "movie_name": "lights out",
                "slots":[ {
                    "slot_id": "146",
                    "screen_id": "45",
                    "time": "11.00",
                    "price": "150"
                }
                {
                    "slot_id": "147",
                    "screen_id": "45",
                    "time": "2.00",
                    "price": "103"
                }
                {
                    "slot_id": "155",
                    "screen_id": "45",
                    "time": "11",
                    "price": "11"
                }
               ]
            },
            {
                "movie_name": "conjuring",
                "slots": {
                    "slot_id": "148",
                    "screen_id": "45",
                    "time": "9.00",
                    "price": "500"
                }
            },
            {
                "movie_name": "iceage",
                "slots":[ {
                    "slot_id": "142",
                    "screen_id": "45",
                    "time": "6.00",
                    "price": "204"
                }
                {
                    "slot_id": "151",
                    "screen_id": "45",
                    "time": "",
                    "price": "11"
                }
               ]
            },
        ]
  }

How should i change my query for getting above result. I tried with this PHP codes:

                            foreach ($slots as $row) {
                                $movie_name = $row['movie_name'];
                                if (!isset($groups[$movie_name])) {
                                    $groups[$movie_name] = array();
                                }
                            $groups[$movie_name][] = $row;
                            }

But not getting required result. Please help. Thank You..

Answer 1

$json_arr = array("movies"=>array());
foreach ($slots as $slot) {
    $json_arr['movies'][$slot["movie_name"]]['movie_name'] = $slot["movie_name"];
    $json_arr['movies'][$slot["movie_name"]]['slots'][] = array (
        "slot_id" => $slot["slot_id"],
        "screen_id" => $slot["screen_id"],
        "time" => $slot["time"],
        "price" => $slot["price"]
    );
}