php - 如何显示主要成员(member)列表和次要成员(member)详细信息

Question

我正在使用CodeIgniter，我的问题是关于MySQL查询。我有两个表，分别是成员和关系。

成员表

我正在做的是，根据member_type将所有用户添加到成员表中。如果 member_type 为 1，则为主要成员；如果为 2，则为次要成员

关系表

在关系表中，我定义了用户的关系。例如，primary_member_id 为 1，然后次要成员为 3 和 6，次要成员详细信息可在成员表中找到。

现在我需要一个查询来显示列表中的所有主要成员以及与其次要成员名称和其他详细信息相关的信息。

我正在使用带有子行的数据表。这样我就可以在列表中显示主要名称，然后单击加号以显示次要成员。

我尝试了下面的加入代码。

SELECT * FROM `member` LEFT JOIN relation on member.id=relation.primary_member_id WHERE member.member_type=1

下面是上述查询的输出。在此，我两次获取主要成员名称以及如何显示次要成员名称

我需要一个输出

数据表

     function format ( d ) {
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>Full name:</td>'+
            '<td>'+d.name+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Mobile number:</td>'+
            '<td>'+d.phone+'</td>'+
        '</tr>'+
        '<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": "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" }
            ],
               "order": [[1, 'asc']],


            } );
    $('#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');
        }
    } );

} );

ajax

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();
    $books_of_secondary = $this->Search_model->getTotalList_of_secondary();

    $data['draw'] = 1;
    $data['recordsTotal'] = count($books);
    $data['recordsFiltered'] = count($books);
    foreach ($books as $key => $row) 
    {
        $arr_result = array(
                   "profile_pic" => $row->profile_pic,
                   "name" => $row->first_name.' ' .$row->last_name,
                    "phone" => $row->phone
        );
        $data['data'][] = $arr_result;
      }
      //print_r($arr_result);
    echo json_encode($data);
    exit;
}

/型号/

public function getTotalList_of_primary(){
      $this->db->select('*');
      $this->db->from('member');
      $this->db->where('member_type',1);
      $query = $this->db->get();
      $res   = $query->result();        
     return $res;
}

public function getTotalList_of_secondary(){
    /*what query I have to use here*/
}

Answer 1

遵循使用详细信息弹出窗口的想法，如details row中所示， 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
        );

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

然后在格式函数中，您将访问 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;
}