Child Row data from Mysql

simpleman

Hi All,

I have searched this forum + google but not getting my head around this.

I want to use the rows functionality as shown here https://datatables.net/examples/api/row_details.html

But i need to be able to populate the rows with data from a mysql database query. I cant figure out how to do this.

Has any one got a working example of this please that I can follow and learn?

Thanks!

Regards,
R

kthorngren

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

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

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

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.