I am trying to use the full calendar scheduler plugin and I am trying to create the required JSON from my database. The format needed is shown below. I need to the staff do be "children" of their department
So all the cleaners would show under cleaning, human resources under human resources etc
[
{
id: '1',
title: 'Cleaning',
children: [
{
id: '2',
title: 'Cleaner 1'
},
{
id: '2',
title: 'Cleaner 2'
}
]
},
{
id: '2',
title: 'Human Resources',
children: [
{
id: '2',
title: 'Human resources 1'
},
{
id: '2',
title: 'Human resources 2'
}
]
}
]
My tables are shown below:
Groups table:
ID | GROUP_NAME
1 | Human Resources
2 | Cleaning
3 | Warehouse
Staff table
ID | GROUP_ID | STAFF_NAME
1 | 1 | Human resources 1
2 | 1 | Human resources 2
3 | 2 | Cleaning 1
4 | 2 | Cleaning 2
5 | 3 | Warehouse 1
My code so far, it just outputs everything
$data = array();
$getgroups = mysqli_query($con, "SELECT groups.id, groups.group_name, person.name, person.id AS personid FROM groups INNER JOIN interns ON groups.id = interns.group_id");
while($group = mysqli_fetch_assoc($getgroups)) {
$data[] = array(
"id" => $group["id"], //Group ID
"title" => $group["group_name"], //Group Name
"children" => array([
"id" => $group["id"],
"title" => $group["personid"], //Intern name
])
);
}
echo json_encode($data);