Pfthb

I am using CodeIgniter and data table with child row. I have a table and records are: -

id| Name | role | leaders
1 | A | 1 | 1
2 | B | 2 | 1
3 | C | 2 | 1
4 | D | 3 | 2
5 | E | 3 | 2
6 | F | 4 | 3
7 | G | 4 | 3

Note: leaders value are the id value.

Now I have to display the parent records which the role is 1,2,3.
Like I have Name A,B,C,D,E with roles are 1,2,2,3,3

A, B, C, D, E records are displaying like parent records and I have to display the child record related to the leader's id.

print_r($books_of_secondary)

For example: A is the parent and child records are B, C. Same as on B is the parent and child are D and E. C is the parent and child are F and G So my output is like below

id| Name | role | leaders
1 | A | 1 | 1
 |
 |->B | 2 | 1
 |->C | 2 | 1

2 | B | 2 | 1
 |
 |->D | 3 | 2
 |->E | 3 | 2

3 | c | 2 | 1
 |
 |->F | 4 | 3
 |->G | 4 | 3

4 | D | 3 | 2
 |
 |-> NO records

5 | E | 3 | 2
 |
 |-> NO records

I tried some code which is

Controller

public function team_members_get()
 $draw = intval($this->input->get("draw"));
 $start = intval($this->input->get("start"));
 $length = intval($this->input->get("length"));
 $books = $this->Employee_model->getTotalList_of_TeamLeader();

 $data['draw'] = 1;
 $data['recordsTotal'] = count($books);
 $data['recordsFiltered'] = count($books);
 foreach ($books as $key => $row) 
 
 $encryption_id=base64_encode($this->encryption->encrypt($row->id));
 $arr_result = array(
 // "Sr.No" => $n,
 "id" => $encryption_id,
 "TLname" => $row->firstname.' ' .$row->lastname,
 "emp_role_name" => $row->emp_role_name,
 "emp_teamLeader" => $row->team_leadername,

 );
 $array_secondary = array();
 $books_of_secondary = $this->Employee_model->getTotalList_of_TeamLeader_employee($row->team_leadername);
 foreach ($books_of_secondary as $key => $row) 
 

 $arr_result2 = array(
 "t_id" => base64_encode($this->encryption->encrypt($row->id)),
 "t_name" => $row->firstname.' ' .$row->lastname,
 "t_emp_role_name" => $row->emp_role_name,
 "t_emp_teamLeader" => $row->team_leadername
 );
 $array_secondary = $arr_result2;
 
 $arr_result['secondary'] = $array_secondary; 
 $data['data'] = $arr_result;
 
 echo json_encode($data);
 exit;
 

Model

public function getTotalList_of_TeamLeader()
 $get_member ="access_role IN(1,2,3) AND is_archive=0";
 $this->db->select('*');
 $this->db->from('tbl_employee'); 
 $this->db->where($get_member);
 $query = $this->db->get();
 $res = $query->result(); 
 return $res;
 
 public function getTotalList_of_TeamLeader_employee($team_leadername)
 $this->db->select('*');
 $this->db->from('tbl_employee');
 $this->db->where('team_leadername',$team_leadername);
 $query = $this->db->get();
 $res = $query->result();
 return $res;
 

Js

 function format(d) 
 // d is the original data object for the row var val;
 if(d.secondary.length == 0) 
 return "There are no Team members";
 
 var display = '<table cellpadding="5" cellspacing="0" border="0" width="100%">'; 

 for (val of d.secondary) 
 var s_action_set="<a href='' class='edit_color s_icon_set tooltip_'><img src='"+baseUrl+"/assets/images/icons/pending.png'><span class=tooltiptext_>Edit</span></a>";
 display += '<tr><td width="2%"></td>' + '<td>' + val.t_name + '</td>' + '<td>' + val.t_emp_role_name + '</td>'+ '</td>' + '<td>' + val.t_emp_teamLeader + '</td>'+ '</tr>';
 
 display += '</table>';
 return display;


$(document).ready(function() 
 var oTable =$('#team_members_list').DataTable( 
 "responsive": true,
 "paging": true,
 "pageLength" : 10,
 "ajax": 
 "url": baseUrl+ "/Employee_control/team_members_get",
 "type": "POST"
 ,
 "columns": [
 "data": "TLname" ,
 "data": "emp_role_name" ,
 "data": "emp_teamLeader" , 
 ],
 );
 $('#team_members_list tbody').on('click', 'td.details-control', function () 
 var tr = $(this).closest('tr');
 var row = oTable.row( tr );

 if ( row.child.isShown() ) 
 // This row is already open - close it
 row.child.hide();
 tr.removeClass('shown');
 
 else 
 // Open this row
 row.child( format(row.data()) ).show();
 tr.addClass('shown');

 //$('[data-toggle="tooltip"]', tr.next('tr')).tooltip();
 
 );
);

you have to write self join query.

select te.name as leaderName,te1.name as childName from tbl_employee as te join tbl_employee as te1 on te.id = te1.leaders

this query gives you leader name with child name.