I have a few tables in my MySQL database which are interconnected with foreign keys and I want to retrieve data from these for my REST API. So far I am successfully able to do it but I want to chain/group data from MYSQL for my REST API. Currently, I am getting my data like this
{
"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"
}
]
}
Demo Link of the current response format
But I believe this is not the right approach, as you can see Floors data is being repeated for each Room and also this structure might be difficult to parse at the client side. I have worked with API data from MongoDB and I think a similar structure would be the best one for my data but I have no idea how to do it using MYSQL and PHP.
I have formatted the JSON which is given below myself to show how I need the response to be.
{
"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"
}
]
}
]
}
}
Demo Link of the required response format
I am using Php Codeigniter framework and here is my data fetching method in model class:
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;
}
}
And this is my code from api controller:
$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);
}
Could someone please help me to achieve the desired ouput.
