如何从 mysql 链接数据，如 mongodb，以便作为 json 传递到 api

Question

我的MySQL数据库中有几个表，它们与外键互连，我想从这些表中检索数据用于我的REST API。到目前为止，我已经成功地做到了这一点，但我想为我的REST API链接/分组来自MYSQL的数据。目前，我正在像这样获取数据

{
"status":"success",
"data":[
{
"floor_id":"1",
"fk_client_id":"1",
"floor_name":"first",
"floor_created_on":"2018-07-07 05:20:06",
"floor_active":"1",
"room_id":"1",
"fk_floor_id":"1",
"fk_type_id":"1",
"room_number":"101",
"room_created_on":"2018-07-07 05:20:44",
"room_active":"1"
},
{
"floor_id":"1",
"fk_client_id":"1",
"floor_name":"first",
"floor_created_on":"2018-07-07 05:20:06",
"floor_active":"1",
"room_id":"4",
"fk_floor_id":"1",
"fk_type_id":"2",
"room_number":"104",
"room_created_on":"2018-07-08 17:49:35",
"room_active":"1"
},
{
"floor_id":"3",
"fk_client_id":"1",
"floor_name":"third",
"floor_created_on":"2018-07-07 05:20:21",
"floor_active":"1",
"room_id":"3",
"fk_floor_id":"3",
"fk_type_id":"3",
"room_number":"301",
"room_created_on":"2018-07-07 05:21:26",
"room_active":"1"
}
]
}

当前响应格式的演示链接

但我相信这不是正确的方法，因为您可以看到每个房间的楼层数据正在重复，而且这种结构可能很难在客户端解析。我使用过MongoDB的API数据，我认为类似的结构对于我的数据来说是最好的，但我不知道如何使用MYSQL和PHP来做到这一点。

我已经格式化了自己下面给出的 JSON，以显示我需要的响应方式。

{
"status":"success",
"data":{
"floors":[
{
"floor_id":"1",
"fk_client_id":"1",
"floor_name":"first",
"floor_created_on":"2018-07-07 05:20:06",
"floor_active":"1",
"rooms":[
{
"room_id":"1",
"fk_floor_id":"1",
"fk_type_id":"1",
"room_number":"101",
"room_created_on":"2018-07-07 05:20:44",
"room_active":"1"
},
{
"room_id":"4",
"fk_floor_id":"1",
"fk_type_id":"2",
"room_number":"104",
"room_created_on":"2018-07-08 17:49:35",
"room_active":"1"
}
]
},
{
"floor_id":"3",
"fk_client_id":"1",
"floor_name":"third",
"floor_created_on":"2018-07-07 05:20:21",
"floor_active":"1",
"rooms":[
{
"room_id":"3",
"fk_floor_id":"3",
"fk_type_id":"3",
"room_number":"301",
"room_created_on":"2018-07-07 05:21:26",
"room_active":"1"
}
]
}
]
}
}

所需响应格式的演示链接

我正在使用Php Codeigniter框架，这是我在模型类中的数据获取方法：

public function getdata($client_id) 
{
$this - > db - > select('*');
$this - > db - > from('floor_tb');
$this - > db - > order_by('floor_tb.floor_id');
$this - > db - > join('room_tb', 'room_tb.fk_floor_id=floor_tb.floor_id');
$this - > db - > where("floor_tb.fk_client_id", $client_id);
$this - > db - > where("floor_tb.floor_active", '1');
$this - > db - > where("room_tb.room_active", '1');
$mytable = $this - > db - > get();
if ($mytable) 
{
return $mytable - > result_array();
} else 
{
return false;
}
}

这是我来自 api 控制器的代码：

$data = $this - > Data_Model - > getdata($client_id);
if (!$data) {
$failmsg = array('status' => 'fail', 'message' => 'no data found');
$this - > response($failmsg, 201);
} else {
$successmsg = array('status' => 'success', 'data' => $data);
$this - > response($successmsg, 200);
}

有人可以帮我实现所需的输出吗？

Answer 1

你看过MySQL 8的JSON_OBJECTAGG函数吗？ 它适用于 JSON 和非 JSON 列