parent child datatables

SalmanS · December 2018

Hi,

Its been a while, I am back again. trying to get parent and child datables on click of parent row.

here is my code

`<!DOCTYPE html>
<html>
  <head>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.18/datatables.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.18/datatables.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <meta charset=utf-8 />
    <title>DataTables -  Parent and Child</title>
  </head>
  <body>
    <div class="container">
      <table id="example" class="display nowrap" width="100%">
        <thead>
            <tr>

                <th>Id</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Address</th>
            </tr>
        </thead>

        <tfoot>
            <tr>

                 <th>Id</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Address</th>
            </tr>
        </tfoot>

        <tbody>
          <tr>

            <td>1</td>
            <td>xxx</td>
            <td>uyy</td>
            <td>dddd</td>
          </tr>
          <tr>

            <td>2</td>
            <td>dfff</td>
            <td>ddd</td>
            <td>dddd</td>
          </tr>
        </tbody>
      </table>
    </div>

<div>
      <table id="child" class="LogDetails"> 
        <thead>
            <tr>

                <th>Id</th>
                <th>Loged Date</th>
                <th>Loged Out Time</th>

            </tr>
        </thead>


      </table>
    </div>

  </body>
</html>`

my javascript

$(document).ready(function() {
    var table = $('#example').DataTable( {
        "columns": [
            {   "orderable":      true,
                "data":           null,
                "defaultContent": ''
            },
            { "data": "A" },
            { "data": "B" },
            { "data": "C" },
            { "data": "D" }
        ],
        "order": [[1, 'asc']]
    } );

    // Add event listener for opening and closing details
    $('#example tbody').on( 'click', 'button', function () {
    var data = table.row( $(this).parents('tr') ).data();
    editMember(data[0]);
} );

} );

Anyone around to help, so i can use ssp class to retrieve data from my sql database please?

OR

Just from the text file the child data?_

SalmanS · December 2018

Sorry, the add event listner for opening and closing details

// Add event listener for opening and closing details
    $('#example tbody').on( 'click', 'tr', function () {
    var data = table.row( $(this).parents('tr') ).data();
    detailview(data[0]);

kthorngren · December 2018

Have you looked through this tech note?
https://datatables.net/blog/2016-03-25

If you are not using editor you can ignore those config sections.

Kevin

SalmanS · December 2018

Yes Kevin,
I can see that its available in editor version, is there a way i can achieve using datatables please?

$('#example').on('click', 'tr', function () {

kthorngren · December 2018

Yes, you can use that example to build just the Datatables portion.

$('#example').on('click', 'tr', function () {

The example uses the Select Extension to select the rows. The child table will use an ajax request similar to this from the example:

    ajax: {
        url: '../php/users.php',
        type: 'post',
        data: function ( d ) {
            var selected = siteTable.row( { selected: true } );
 
            if ( selected.any() ) {
                d.site = selected.data().id;
            }
        }
    },

It sends the parent row ID to the server using the ajax data option. The server script will receive the ID parameter and run a query to fetch the desired rows for the child and return them to the client.

Like I said just ignore the Editor config pieces.

Kevin

SalmanS · December 2018

Thanks Kevin, Please see my code? two issue now.

i cannot see my records in table one
where do i include your code.

listusers.php
`

<?php
/* * DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simply to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'people';

// Table's primary key
$primaryKey = 'PersonId';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'personId', 'dt' => 0 ),
    array( 'db' => 'Name',  'dt' => 1 ),
    array( 'db' => 'Department',   'dt' => 2 ),
    array( 'db' => 'RecordDate','dt' => 3,
        'formatter' => function( $d, $row ) {
            return date( 'd-m-Y', strtotime($d));
        })



);

// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'office_employee',
    'host' => ''
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'lib\ssp.class.php' );

echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $where )
//SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "status is null" )

);`

Index.php

<?php

require_once 'dbconfig.php';
 ?>
<!DOCTYPE html>
<html>
  <head>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.18/datatables.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.18/datatables.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <meta charset=utf-8 />
    <title>DataTables -  Parent and Child</title>
  </head>
  <body>
    <div class="container">
      <table id="example" class="display nowrap" width="100%">
        <thead>
            <tr>

                <th>Id</th>
                <th>Name</th>
                <th>Department</th>
                <th>Logged_Date</th>
            </tr>
        </thead>

        <tfoot>
            <tr>

                <th>Id</th>
                <th>Name</th>
                <th>Department</th>
                <th>Logged_Date</th>
            </tr>
        </tfoot>


      </table>
    </div>

<div>
      <table id="child" class="LogDetails">


      </table>
    </div>

  </body>
</html>

<script>

$(document).ready(function() {
        $('#example').DataTable({
            "aLengthMenu": [
                [5, 10, 15, -1],
                [5, 10, 15, "All"]
            ],
            "responsive": true,
            "processing": true,
            "serverSide": true,
            "type": "POST",
            "datatype": "json",
            "ajax": {
                "url": "server_listusers.php",

            },

            "columns": [

                {
                    "data": "0",
                    "EmployeeID": "0",
                    "autoWidth": true
                },
                {
                    "data": "1",
                    "Name": "1",
                    "autoWidth": true
                },
                {
                    "data": "2",
                    "Department": "2",
                    "autoWidth": true
                },
                {
                    "data": "3",
                    "Loged_Date": "3",
                    "autoWidth": true
                }
            ]

        });
      });   

</script>

dbconfig.php //no erros when run in the browser - that means the connection is fine.

<?php


        $db_host = "localhost";

        $db_name = "office_employee";
        $db_user = "root";
        $db_pass = "";

        try{

            $db_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
            $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch(PDOException $e){
            echo $e->getMessage();
        }


    ?>

SalmanS · December 2018

Hi Colin/Kevin,
Can you guys see what I am doing wrong in my code above.

SalmanS · December 2018

Hi Kevin,

I tried using the suggestion earlier but datatables just saying processing message. here is my modified code:

    <script>
        $(document).ready(function() {
            $('#example').DataTable({
                "aLengthMenu": [
                    [5, 10, 15, 100],
                    [5, 10, 15, 100]
                ],
                "responsive": true,
                "processing": true,
                "serverSide": true,
                "type": "POST",
                "datatype": "json",
                "ajax": {
                    "url": "server_isnull.php",

                    data: function ( d ) {
            var selected = example.row( { selected: true } );

            if ( selected.any() ) {
                d.site = selected.data().UserName;
                alert("selected  :" + d.site);
            }
        }

                },

                "columns": [

                    {
                        "data": "0",
                        "UserName": "0",
                        "autoWidth": true
                    },
                    {
                        "data": "1",
                        "First_Name": "1",
                        "autoWidth": true
                    },
                    {
                        "data": "2",
                        "Last_Name": "2",
                        "autoWidth": true
                    },
                    {
                        "data": "3",
                        "Gender": "3",
                        "autoWidth": true
                    },
                    {
                        "data": "4",
                        "Status": "4",
                        "autoWidth": true
                    }
                ],
                "destroy": true

            });






        });

    </script>

kthorngren · December 2018

datatables just saying processing message

Do you get any alert error messages?

Do you get any browsers console errors?

Have you looked at the browser's developer tools and used the network tab to see what the XHR request and response is?

Have you looked at your server script for errors?

Kevin

SalmanS · December 2018

No errors,everything works fine. but the moment you try to add the suggested code as below. the datatables stopped working, processing message.

    data: function ( d ) {
            var selected = example.row( { selected: true } );

            if ( selected.any() ) {
                d.site = selected.data().UserName;
                alert("selected  :" + d.site);
            }
        }

If you remove the above code all works fine.
Not sure what I am doing wrong?

kthorngren · December 2018

You don't get any console errors when you add the above code?

That code is for the "child" table as showing here:
https://datatables.net/blog/2016-03-25#Child-table

Not sure if the '#example' table is the parent or child. If have var selected = example.row( { selected: true } );. Where is the example variable defined.

Kevin

SalmanS · December 2018

This is the master table: parent table: #example is the ID: so first i am trying to gather the value of the row column... in this case name [0]. once I have the value than...display or build the child table, based on the value of column name[0].

Where is the example variable defined.---- example is the parent table.. there is no child table in my code yet. first want to alert the value .onclick or .click of the respective row.

kthorngren · December 2018

In the ajax is not the place for that code. It belongs in the child table to send an ajax request just for that id. You can use this example to see how the row selection is working.

Kevin

SalmanS · December 2018

I am terribly confused.

I got a master/parent table named #example and child table #users.

which code is going where.? do i need any code in my master or parent table...

or do i need code in both tables?..

is there any live example of what codes goes where.

parent table #example (Site)
child table #users (Users - List of users on each site)

SalmanS · December 2018

more simpler another example:

Customers:

CustomerID CustomerName
———- —————
1 ABC Foundation
2 The XLZ Company

Products:

CustomerID Product Sales
———- ———- —–
1 Product 1 $200
1 Product 2 $437
1 Product 3 $523
2 Product 1 $240
2 Product 2 $892
2 Product 3 $395

kthorngren · December 2018

Since you are not using Editor there isn't much you need to do with the master/parent table. It needs to have the select extension and set for single row selection.

The child table will use the ajax.data option to send the ID to the server script for the child table.

I don't have a way to put together a good example as I don't have a way to simulate a server script to retrieve the child data. I did put together this example though:
http://live.datatables.net/biliniyu/1/edit

It uses the same ajax url for both the parent and child tables. Likely yours will be different. Also it uses the ajax.dataSrc to filter the returned data from the server to display only the child row matching the name of the row selected in the parent. This would not be used in a normal parent/child solution. It is expected that the child script returns only the data expected.

Kevin

SalmanS · December 2018

I don't have words to thank you, the concept is so clear with that example. There are only two true champions in this world one is Kevin and another is Allan. Love you both.

kthorngren · December 2018

Glad that example helped.

Kevin

SalmanS · December 2018

Last one week was so stressful for this issue, I was almost giving up on DATATables and thought of going back to PHP pagination and set our own rules. but this example means a lot to me.

SalmanS · December 2018

Hi Kevin, What is the format of objects.txt file please? Comma delimited or with header?

here is my objects.txt file:

"name";"position";"office";"extn";"start_date"; "salary"
"Mike"; "Director"; "UK"; "ext. 123"; "12/12/2018"; "5000"
"DanMike"; "Director"; "UK"; "ext. 123"; "12/12/2018"; "5000"
"Tan"; "Director"; "UK"; "ext. 123"; "12/12/2018"; "5000"
"Blonde"; "Champion"; "UK; ext."; "123"; "12/12/2018"; "5000"
"Beauty"; "Director"; "UK"; "ext. 123"; "12/12/2018"; "5000"

Please clarify?

kthorngren · December 2018

That is something supplied by Datatables which is why I didn't have a way to have a proper child script.
https://datatables.net/manual/tech-notes/9

You can see the format by looking at this example and clicking the ajax tab:
https://datatables.net/examples/ajax/objects.html

Kevin

SalmanS · December 2018

many thanks got it. all working on my local pc.

SalmanS · December 2018

But if you have multiple corresponding records in your child, it is just showing one record in the child table, though there are more than corresponding records in my child table.

I separated
Parent.txt (unique Records)

and child.txt (multiple records)

The child.txt contains multiple records for the same person "Tiger Nixon" the corresponding records for selected parent. see below:

"data": [
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},
{
"id": "1",
"name": "Tiger Nixon",
"position": "System Architect",
"salary": "$320,800",
"start_date": "2011/04/25",
"office": "Edinburgh",
"extn": "5421"
},

kthorngren · December 2018

How are you retrieving the data from the child data?

That will control the number of rows shown in the child table.

Kevin

SalmanS · December 2018

I separated
Parent.txt (unique Records)

and child.txt (multiple records)

The child.txt contains multiple records for the same person "Tiger Nixon" the corresponding records for selected parent. and changed the corresponding ajax call

ie.,

    Parent:
    var parentTable = $('#parent').DataTable( {
            ajax: "parent.txt",

    Child:

      var childTable = $('#child').DataTable( {
            ajax: {
              url: "ajax/child.txt",  // This is the URL to the server script for the child data
              data: function ( d ) {

                // Get selected row
                var selected = parentTable.row( { selected: true } );

                if ( selected.any() ) {
                    d.name = selected.data().name;
                }
              },


              dataSrc: function (data) {
                var selected = parentTable.row( { selected: true } );

                if ( selected.any() ) {
                    var name = selected.data().name;
                    for (i=0; i < data.data.length; i++) {
                      var row = data.data[i];
                      if (row.name === name) {
                        return [row];
                      }
                    }
                } else {
                return [];
                }
              }
            },
            columns: [
                { "data": "name" },
                { "data": "office" },
                { "data": "extn" },
                { "data": "start_date" },
                { "data": "salary" }
            ]

kthorngren · December 2018

You would need to build an array and return the array instead of the individual row. See if these changes work:

          dataSrc: function (data) {
            var selected = parentTable.row( { selected: true } );
            var rows = [];
 
            if ( selected.any() ) {
                var name = selected.data().name;
                for (i=0; i < data.data.length; i++) {
                  var row = data.data[i];
                  if (row.name === name) {
                    rows.push(row);
                  }
               }
            } 
            return rows;
            }
          }

Didn't try it so hope it works.

Kevin

SalmanS · December 2018

It worked with the changes below:? However, when you do search in parent table - the child table still showing corresponding records for the previously selected parent record.

    var childTable = $('#child').DataTable( {
            ajax: {
              url: "ajax/child.txt",  // This is the URL to the server script for the child data
              data: function ( d ) {


                var selected = parentTable.row( { selected: true } );


                if ( selected.any() ) {
                    d.name = selected.data().name;
                }
              },


              dataSrc: function (data) {
      var selected = parentTable.row( { selected: true } );
      var rows = [];

      if ( selected.any() ) {
          var name = selected.data().name;
          for (i=0; i < data.data.length; i++) {
            var row = data.data[i];
            if (row.name === name) {
              rows.push(row);
            }
         }
      }
      return rows;
      }



            },
            columns: [
                { "data": "name" },
                { "data": "office" },
                { "data": "extn" },
                { "data": "start_date" },
                { "data": "salary" }
            ]
        } );

kthorngren · December 2018

when you do search in parent table - the child table still showing corresponding records for the previously selected parent record.

If you want to clear the child table on searching then you will probably need to use the search event. In the event use rows().deselect() to deselect all rows followed by childTable.ajax.reload().

Kevin

SalmanS · December 2018

tried the following - no console error and still the child table showing old records.
class of my parent table is '.display'

var parentTable = $('#parent').DataTable( {
        ajax: "parent.txt",
        pageLength: 5,
        select: {
          style: 'single'
        },
        columns: [
            { "data": "name" },
            { "data": "position" },
            { "data": "office" },
        ]


    } );
  parentTable.on( 'search.dt', function () {
    parentTable.rows('.display').deselect();
} );

kthorngren · December 2018

Try this:

  parentTable.on( 'search.dt', function () {
    parentTable.rows().deselect();
    childTable.ajax.reload();
} );

Kevin

SalmanS · December 2018

perfecto...the last part is when you select show entries from the drop down 10,25 etc. its showing error message in the console.

Parent_Child.html:1 [Violation] Added non-passive event listener to a scroll-blocking 'mousewheel' event. Consider marking event handler as 'passive' to make the page more responsive. See https://www.chromestatus.com/feature/5745543795965952

parent child datatables

parent child datatables

Answers

Howdy, Stranger!

Categories

DataTables

parent child datatables

parent child datatables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables