Child Row data from Mysql

Child Row data from Mysql

simplemansimpleman Posts: 6Questions: 2Answers: 1

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

This question has an accepted answers - jump to answer

Answers

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

    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

  • simplemansimpleman Posts: 6Questions: 2Answers: 1

    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 > ``` ?>
  • kthorngrenkthorngren Posts: 21,247Questions: 26Answers: 4,929

    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

  • simplemansimpleman Posts: 6Questions: 2Answers: 1
    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.

This discussion has been closed.