php - How to display the secondary records in the data table child row with respective id?

Question

I am using CodeIgniter and data table with child row. What I am doing is I have a master table with a primary member and a secondary member. Primary member can identify using member_type=1 and secondary member member_type=2. Both members are connected with the relation table. I am able to display the records in the data table with the primary member as well as a secondary member.

Now I am using the data table child row plugin(https://datatables.net/examples/api/row_details.html). I have to display the primary member in the list if the user wants to check the secondary member of the specific primary member then should click on plus sign image and they will get the secondary member list.

So the output will be

Explanation of the code

Now I am calling two model from the Listdetails_ajax(). First for the primary records(getTotalList_of_primary();) and second for the secondary records(getTotalList_of_secondary()). In the first model, I get the primary details with id and that id I am passing to the second model to get the secondary member record related to the id.

Issue is,

1) I am getting the all the records in the data table(I need only primary records)

2) How do I assign the child value in the data table?

Would you help me out with this issue? Controller

public function Listdetails_ajax(){  
   $draw = intval($this->input->get("draw"));
$start = intval($this->input->get("start"));
$length = intval($this->input->get("length"));
$books = $this->Search_model->getTotalList_of_primary();

$data['draw'] = 1;
$data['recordsTotal'] = count($books);
$data['recordsFiltered'] = count($books);
foreach ($books as $key => $row) 
{
    //print_r($row->customer_id);
    $arr_result = array(
                "member_id" => base64_encode($this->encryption->encrypt($row->member_id)),
                "profile_pic" => $row->profile_pic,
                "name" => $row->first_name.' ' .$row->last_name,
                "phone" => $row->phone,
                "chss_no" => $row->chss_no,
                "emp_id" => $row->emp_id,
                "address" => $row->address
                // "member_type" => $row->member_type
    );
     $data['data'][] = $arr_result;
     $books_of_secondary = $this->Search_model->getTotalList_of_secondary($row->customer_id);
     print_r($arr_result);
     foreach ($books_of_secondary as $key => $row) 
        {
            //print_r($row->customer_id);
            $arr_result2 = array(
                        "s_member_id" => base64_encode($this->encryption->encrypt($row->member_id)),
                        "s_profile_pic" => $row->profile_pic,
                        "s_name" => $row->first_name.' ' .$row->last_name,
                        "s_phone" => $row->phone,
                        "s_chss_no" => $row->chss_no,
                        "s_emp_id" => $row->emp_id,
                        "s_address" => $row->address
            );


            $data['data'][] = $arr_result2;
              print_r($arr_result2);
        }
  }
echo json_encode($data);
exit;
}

Model

public function getTotalList_of_primary(){
      $this->db->select('*');
      $this->db->from('members');
      $this->db->where('member_type',1);
      $query = $this->db->get();
      $res   = $query->result();        
     return $res;
}
public function getTotalList_of_secondary($id){
      $this->db->select('*');
      $this->db->from('members'); 
      $this->db->join('relation_member', 'relation_member.secondary_member_id = members.member_id'); 
      $this->db->where('relation_member.primary_customer_id',$id);
      $query = $this->db->get();
      $res   = $query->result();
      return $res;
}

Datatable

function format ( d ) {
    // `d` is the original data object for the row
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Full name:</td>'+
            '<td>'+d.s_name+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Mobile number:</td>'+
            '<td>'+d.s_phone+'</td>'+
        '</tr>'+     
    '</table>';
}

$(document).ready(function() {
            var oTable =$('#all_list').DataTable( {
                "processing": true,
                // "serverSide": true,
                "pageLength": 10,
                "ajax": {
                    "url": baseUrl+ "/index.php/Search/Listdetails_ajax",
                    "type": "POST"
                },
                "columns": [
                   {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
                    },
                { "data": "member_id","className": "display_none"},
                { "data": "profile_pic","className":"img_list_pic",
                "render": function (data, type, full, meta) {
                return '<img src='+baseUrl+'/uploads/images/'+data+' class="search_pic">';
                }
                 },
                { "data": "name" },
                { "data": "phone" },
                { "data": "chss_no" },
                { "data": "emp_id" },
                { "data": "address" },
            ],


            } );
            // Add event listener for opening and closing details
    // Add event listener for opening and closing details
     // Add event listener for opening and closing details
    $('#all_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();
        }
    } );

} );

Json

Array
(
    [member_id] => M2EwYzhmNzBjYTYzMTdiMzFiZDFjNGIwY2Y1NmYwNmQ3ZWY0MGEzNTg5OTY2YjBkNWUwZjc3MWY4NWJiZTdmMGM3Y2Y4YTdhZGI4MTM1MTMyMDVmMDQ1NjRkZTVmMmViZjYzMjcxZGVlODhmNzg1OGMzM2M1ZWI0MjUwYjQ5YzVOUDZSRlZKcFhaNWZ0eTg3NVlBT09HcFhwcW5XSGlIaWpYelJpOTduNzI0PQ==
    [profile_pic] => 5b1b88a7cd85a1528531111090618.png
    [name] => alpha bita
    [mobileno] => 9898981234
    [emp_id] => EMP_2018_03
    [address] => Navi Mumbai
)
Array
(
    [s_member_id] => ZWFlMjQwNGEwMGQ2YWVjMzlkZGRkN2Y1ZTQ1ODQ1NDc0N2M5ZDRiZDY3NDkyZDQ2YjNkN2UxZjI4ZTFhY2Y1Y2U1NWI2N2RlMDEyY2M5NDE3NDc2NWNiZmU4MzFjNWYwYzc2OWQxNjIzZDJiM2QwMzg0ODU2OWNjNDY5OWI4MWM0Yjl0YUgzOUVKaGNka2ZuTEp4Tk1EZitraTEyd2NtLzliNk8wSlpQU0FJPQ==
    [s_profile_pic] => 
    [s_name] => Anil 
    [s_phone] => 2121212121
    [s_emp_id] => 
    [s_address] => Navi Mumbai
)
Array
(
    [member_id] => ZDVkYTFiMGM0MGRhNzBjMTg1N2VhMDMyYjU0NDQyMDFlNjMyNDNjNGFhZjg0ODM4YTRhNWJiZjBlODkyZGZkYzk5MGFmN2JjZmFmODM0ZTQ3NzA3NTkwZWFlNGIwYzU2MzBmZGQ0NTZlOTkyMWJiNDc2OTdmN2EyZjJkOGM0NDZDNTlRRVFmK1hoMUNZRlM3R0dFMCtvaGZMclplNWxIYk1DOG0vVklwMjBBPQ==
    [profile_pic] => 5b1b8c15eefe31528531989090618.jpg
    [name] => Ajay Waghmare
    [phone] => 8989855451
    [chss_no] => chss/ajay/2018
    [emp_id] => emp_2018_45
    [address] => Andheri Mumbai
)
Array
(
    [s_member_id] => ZTk1NWYwNWVlMmE3ZTUwMjExMDk3NzlmNTE0YmQ5ZGYzNzkyOTU3M2ZjOGE3YWUyZjg0N2I3ODZhMzhmNWNmNTQ2ZTg1MTM4OGMxNzk4MjFmZWYzMzA3YzViODE5YWNjZmIzNDBlYzJlZTUwYzBhYmRjN2RhZmJmMzk5NjAxOTR1SmJyYngwbE04N0wwTVEyK280V0RhVWlKang4VzBlSExyK0VtQTB4eHlNPQ==
    [s_profile_pic] => 5b1b8fae049341528532910090618.jpg
    [s_name] => Rohit Suman
    [s_phone] => 7885458454
    [s_chss_no] => roh/sum/2018/09
    [s_emp_id] => 
    [s_address] => Andheri Mumbai
)
Array
(
    [s_member_id] => MzVlOGFhM2MwN2RkMzNmYzdiMjhiMzdiNDVhOGMzMTVjZjhlOTFlNzg4ODAxNjFiM2JhZTExZDUxNzg2NDU3MDBmODllZmJlZTcxMThlOWFiODQ4YjAwMThmZGRmNDIxOTU0NWZmYzEwZTg5YzQzOWM3MDBhNGVlM2ZjMDQ5OTFjV3FuZGdsbXJtK3A1YUhFaEI1dTBSWDN5RkhKVVBEbFpJTE95eDdNb3Q0PQ==
    [s_profile_pic] => 5b1b9052d56bd1528533074090618.jpg
    [s_name] => Pankaj 
    [s_phone] => 8989989898
    [s_chss_no] => p/2018/09/05
    [s_emp_id] => 
    [s_address] => Andheri Mumbai
)

Answer 1

Following the first idea, using a details popup, the controller would look like this:

public function Listdetails_ajax(){  
   $draw = intval($this->input->get("draw"));
    $start = intval($this->input->get("start"));
    $length = intval($this->input->get("length"));
    $books = $this->Search_model->getTotalList_of_primary();

    $data['draw'] = 1;
    $data['recordsTotal'] = count($books);
    $data['recordsFiltered'] = count($books);
    foreach ($books as $key => $row) 
    {
        //print_r($row->customer_id);
        $arr_result = array(
                    "member_id" => base64_encode($this->encryption->encrypt($row->member_id)),
                    "profile_pic" => $row->profile_pic,
                    "name" => $row->first_name.' ' .$row->last_name,
                    "phone" => $row->phone,
                    "chss_no" => $row->chss_no,
                    "emp_id" => $row->emp_id,
                    "address" => $row->address
                    // "member_type" => $row->member_type
        );

         $array_secondary = array();
         $books_of_secondary = $this->Search_model->getTotalList_of_secondary($row->customer_id);
         foreach ($books_of_secondary as $key => $row) 
            {
                //print_r($row->customer_id);
                $arr_result2 = array(
                            "s_member_id" => base64_encode($this->encryption->encrypt($row->member_id)),
                            "s_profile_pic" => $row->profile_pic,
                            "s_name" => $row->first_name.' ' .$row->last_name,
                            "s_phone" => $row->phone,
                            "s_chss_no" => $row->chss_no,
                            "s_emp_id" => $row->emp_id,
                            "s_address" => $row->address
                );


                $array_secondary[] = $arr_result2;
            }
         $arr_result['secondary'] =  $array_secondary;  
         $data['data'][] = $arr_result;
      }
    echo json_encode($data);
    exit;
}

And then in the format function you would access the secondary field to get an array of secondaries. Maybe like this:

function format(d) {
    // d is the original data object for the row var val;
    if(d.secondary.length == 0) {
        return "There are no secondary members";
    }

    var display = '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'; 
    for (val of d.secondary) { 
        display += '<tr>' + '<td>Full name:</td>' + '<td>' + val.s_name + '</td>' + '</tr>' + '<tr>' + '<td>Mobile number:</td>' + '<td>' + val.s_phone + '</td>' + '</tr>';
    }
    display += '</table>';
    return display;
}