Answers

• kthorngren Posts: 21,247Questions: 26Answers: 4,929

  December 2016

  Its difficult to help without understanding more about your environment and your specific questions. The example is a good place to learn. Essentially you can copy the code and update the format function to display the additional fields you are interested in.

  Assuming you have a working Datatables config you update the SQL query to include the additional you want to display in the child row. Change the format function, in the example, to display the desired fields. Add the CSS from the example and you should be all set.

  If this doesn't help please post more info and questions.

  Kevin
• simpleman Posts: 6Questions: 2Answers: 1

  December 2016

  Hi Kevin,

  Thanks for responding. The data is farily simple (like my name! ). I have a index.php which gets a json response from response.php which pulls data from mysql. So here is my code! When I try to run this the website is just stuck and does not load anything. What is that I am doing wrong? If there is an example of similar thing (i.e pulling data from MySQL it would be great!

  Thanks in advance Kevin for your input (and any one else who is going to help me out here!)

  Index.php

  
  <script type="text/javascript">
  
  function format (d) {
  
   return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
    '<tr>'+
        '<td>Uni name:</td>'+
        '<td>'+d.iuni_name+'</td>'+
    '</tr>'+
  
    '<tr>'+
        '<td>Address:</td>'+
        '<td>'+d.iaddress+'</td>'+
    '</tr>'+
  
    '<tr>'+
        '<td>class Date:</td>'+
        '<td>'+d.iratingdate+'</td>'+
    '</tr>'+
  
    '<tr>'+
        '<td>Class Grade:</td>'+
        '<td>'+d.irating_value+'</td>'+
    '</tr>'+
  
    '</table>';
  
  }
  
  
  $( document ).ready(function() {
  
  var table = $('#employee_grid').DataTable( {
       "bProcessing": true,
           "serverSide": true,
           "ajax":{
              url :"response-displayrow.php", // json datasource
              type: "post",  // type of method  ,GET/POST/DELETE
              error: function(){
                $("#employee_grid_processing").css("display","none");
              }
            }, //added for + sign extra
           "columns": [
             {
                "class": 'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
             },
             { "data": "iuni_name"},
             { "data": "iaddress"},
             { "data": "iclassdate"},
             { "data": "igrade_value"}
           ],
            "order":[[1,'asc']]
          });
  
  
  // Add event listener for opening and closing details /*
      $('#employee_grid tbody').on('click', 'td.details-control', function () {
          var tr = $(this).closest('tr');
          var row = table.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');
          }
      });
  
  });
  
  </script>
  

  Response.php:

  ```
  <?php
  //include connection file
  include_once("connection.php");

  // initilize all variable
  $params = $columns = $totalRecords = $data = array();
  
  $params = $_REQUEST;
  
  //define index of column
  $columns = array(
      0 =>'uni_name',
      1 =>'address',
      2 => 'classdate',
      3 => 'grade_value'
  );
  
  $where = $sqlTot = $sqlRec = "";
  
  // check search value exist
  if( !empty($params['search']['value']) ) {
      $where .=" WHERE ";
      $where .=" ( uni_name  LIKE '".$params['search']['value']."%' ";
      $where .=" OR address LIKE '".$params['search']['value']."%' ";
      $where .=" OR grade_value LIKE '".$params['search']['value']."%' )";
  }
  
  // getting total number records without any search
  $sql = "SELECT uni_name,  address,classdate, grade_value  FROM `testRun` ";
  $sqlTot .= $sql;
  $sqlRec .= $sql;
  //concatenate search sql if value exist
  if(isset($where) && $where != '') {
  
      $sqlTot .= $where;
      $sqlRec .= $where;
  }
  
  
  $sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";
  
  $queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));
  
  
  $totalRecords = mysqli_num_rows($queryTot);
  
  $queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch uni data");
  
  //iterate on results row and create new index array of data
  while( $row = mysqli_fetch_row($queryRecords) ) {
       $data[] = $row;
  }
  $json_data = array(
          "draw"            => intval( $params['draw'] ),
          "recordsTotal"    => intval( $totalRecords ),
          "recordsFiltered" => intval($totalRecords),
          "data"            => $data   // total data array
          );
  
  echo json_encode($json_data);  // send data as json format
  

  <?php > ``` ?>
• kthorngren Posts: 21,247Questions: 26Answers: 4,929

  December 2016

  I'm not familiar with PHP and not sure what the JSON response looks like. Does the mysqli_fetch_row function prefix the column names with "i"?

  Can you provide the JSON response?

  Do you get any errors in the browser's console?

  Kevin
• simpleman Posts: 6Questions: 2Answers: 1

  January 2017 edited January 2017 Answer ✓

  This is now sorted. For the rest of you if you want some info here is how it looks like

  "ajax":{
              url :"response-displayrow.php", // json datasource
              type: "post",  // type of method  ,GET/POST/DELETE
              error: function(){
                $("#employee_grid_processing").css("display","none");
              }
            },
  
           //added for + sign extra
           columns:[
             {
                "className": 'details-control',
                "orderable":      false,
                "data":        null,
                "defaultContent":  ''
             },
  
             { data: "name"},
             { data: "address" }, etc.///
  

  Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.