将多个mysql结果按一列值分组 [英] Group multiple mysql result by one column value

问题描述

我正在尝试从mysql表获取所有结果并转换为Json格式.每行可以包含一个公共列值(movie_name).我想按该公用字段对结果进行分组.

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`;

结果:

{
        "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"
                }
               ]
            },
        ]
  }

我应该如何更改查询以获得以上结果.我尝试使用以下PHP代码:

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..

推荐答案

$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"]
    );
}

这篇关于将多个mysql结果按一列值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！