Child row data from another Mysql Table

Gerald.Rager

Hi,

i want to create a table with child row for each main row!

example i have 2 Tables First: Table Orders Second: Orderitems

now i want that the main Table Show all Orders
first th(td) of each row has a detail-control button if it is clicked the child-rows for this main-row must appear with data from the orderitems Table

but each row must editable => main-row and child-row

I hope you understand me

Thanks a lot

allan

So you want child tables in the rows of the parent table?

Have you used this example as a basis for creating the structure you want? Have you got it showing child tables (you need to modify the code in the table to have it do that)?

When you have done that, it is simply a case of applying Editor to the child table in order to make it editable like any other DataTable.

Allan

Gerald.Rager

Okay Thank you i will try it

Gerald.Rager

Hallo Allan so the child-row work like the example you post me

now i have do get the data for the child!!!

sorry i forgot i use the php framwork to geht my data from mysql

i want to use the ID from the Main-Row to make a query to get data from another table

ps: the child kan have multiple rows

Thank you

allan

i want to use the ID from the Main-Row to make a query to get data from another table

Use the row().data() method to get data from the host row, just like what is done in the example.

Allan

bejbe01

hey Gerald, if you were able to get it work, do you have some working example which you can share please? I am trying to do the same, childrow with data from another mysql table, I am able to create childrow and format it, but I need to get ID from the parent row (which is not shown, but it is in json) and according it pass it as where to another server side request and parse json result to child

Gerald.Rager

Hey sure i can,

I did it with a workaround i load a datatable when the detail control is clicked

First here is the base table init

    var customertable = $('#customer').DataTable({
    dom: "Tftp",
    ajax: {
        url: "../php/editor/customer.php",
        type: "POST"
    },
    processing: true,
    deferRender: true,
    serverSide: true,
    columns: [
        {
            "className": 'details-control',
            "orderable": false,
            searchable: false,
            "data": null,
            "defaultContent": ''
        },
        {data: "firma"},
        {data: "vorname"},
        {data: "nachname"},
        {data: "email"},
        {
            data: "status",
            render: function (val, type, row) {
                return val == 0 ? "Inaktiv" : "Aktiv";
            }
        },
        {data: "lastlogin"}
    ],
    order: [1, 'asc'],
    tableTools: {
        sRowSelect: "os",
        aButtons: [
            {sExtends: "editor_create", editor: customer},
            {sExtends: "editor_edit", editor: customer},
            {
                sExtends: 'select_single',
                sButtonText: 'Deaktivieren',
                fnClick: function () {
                    if (customertable.row('.active').length !== 0) {
                        changestatus
                            .edit(customertable.row('.active').node(), false)
                            .set('status', false)
                            .submit();
                    }
                }
            },
            {
                sExtends: 'select_single',
                sButtonText: 'Aktivieren',
                fnClick: function () {
                    if (customertable.row('.active').length !== 0) {
                        changestatus
                            .edit(customertable.row('.active').node(), false)
                            .set('status', true)
                            .submit();
                    }
                }
            }
        ]
    }
});

1. now the event listiner for the detail control

   // Add event listener for opening and closing details
   $('#customer tbody').on('click', 'td.details-control', function () {
   
   var tr = $(this).closest('tr');
   var row_1 = customertable.row(tr);
   var id = customertable.row(tr).data().id;
   
   if (row_1.child.isShown()) {
       row_1.child.hide();
       tr.removeClass('shown');
   }
   else {
       customertable.rows().eq(0).each(function (idx) {
           var row_2 = customertable.row(idx);
   
           if (row_2.child.isShown()) {
               row_2.child.hide();
               row_2.$('tr').removeClass('shown');
           }
       });
   
       // Open this row
       row_1.child(format(row_1.data())).show();
       tr.addClass('shown');
   }
   
   var add_licence = new $.fn.dataTable.Editor({
       table: "#licence",
       ajax: "../php/editor/licence.php",
       fields: [{
           label: "Paket:",
           name: "licencekey.paketid",
           type: "select"
       }, {
           label: "Monitoring Status (Intervall):",
           name: "licencekey.statusintervall"
       }, {
           label: "Monitoring Log (Intervall):",
           name: "licencekey.logintervall"
       }, {
           label: "CustomerID",
           name: "licencekey.customerid",
           type: "hidden",
           def: id.toString()
       }, {
           label: "Schlüssel:",
           name: "licencekey.licencekey",
           type: "hidden",
           def: randomKey()
       }
       ]
   });
   
   var licencetable = $('#licence').DataTable({
       dom: "Ttp",
       ajax: {
           url: "../php/editor/licence.php",
           type: "POST"
       },
       scrollCollapse: true,
       processing: true,
       deferRender: true,
       serverSide: true,
       columns: [
           {data: "licencekey.licencekey"},
           {data: "licencepaket.bezeichnung"},
           {data: "licencekey.hash"},
           {data: "licencekey.activatedate"}
       ],
       tableTools: {
           sRowSelect: "os",
           aButtons: [
               {sExtends: "editor_create", editor: add_licence},
               {sExtends: "editor_remove", editor: add_licence},
               {
                   sExtends: 'select_single',
                   sButtonText: 'Freischalten',
                   fnClick: function () {
                       if (licencetable.row('.active').length !== 0) {
                           free
                               .edit(licencetable.row('.active').node(), false)
                               .set('licencekey.hash', null)
                               .set('licencekey.activatedate', new Date(0).toISOString())
                               .submit();
                       }
                   }
               }
           ]
       }
   });
   
   licencetable.ajax.url("../php/editor/licence.php?CustomerID=" + id).load();
   

   });

i hope that help you