时间戳

首页 > 解决方案 > 如何在数据表子行中显示具有相应id的辅助记录？

php - 如何在数据表子行中显示具有相应id的辅助记录？

问题描述

我正在使用 CodeIgniter 和带有子行的数据表。我正在做的是我有一个主表，其中有一个主要成员和一个次要成员。主要成员可以识别使用member_type=1和次要成员member_type=2。两个成员都与关系表相连。我能够在数据表中显示主要成员和次要成员的记录。

现在我正在使用数据表子行插件（https://datatables.net/examples/api/row_details.html）。如果用户想要检查特定主要成员的次要成员，我必须在列表中显示主要成员，然后应该单击加号图像，他们将获得次要成员列表。

所以输出将是

在此处输入图像描述

代码说明

现在我从 Listdetails_ajax() 中调用两个模型。第一个用于主要记录 ( getTotalList_of_primary();)，第二个用于辅助记录 ( getTotalList_of_secondary())。在第一个模型中，我使用 id 获取主要详细信息，并将该 id 传递给第二个模型以获取与 id 相关的辅助成员记录。

问题是，

1）我正在获取数据表中的所有记录（我只需要主记录）

2）如何在数据表中分配子值？

你能帮我解决这个问题吗？ 控制器

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;
}

模型

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;
}

数据表

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
)

标签： phpmysqlhtmlcodeigniterdatatables

解决方案

按照第一个想法，使用详细信息弹出窗口，控制器将如下所示：

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;
}

然后在 format 函数中，您将访问该secondary字段以获取辅助数组。也许是这样的：

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;
}

推荐阅读