Child table has no data

Child table has no data

rslonerslone Posts: 3Questions: 1Answers: 0

Good morning. I am very new to Datatables/Editor.
I followed the Blog tutorial on setting up a Child/Editor, but I've reached a wall.
So far the code will get the parent. and then display the child editor, but its says there is no data.

When i go to the SSP and give it a GET (I changed it all to GET just to see what was being passed), "localhostphp/po_lines.php?po_id=1" It responds with lots of happy JSON data.
However when i use the table, the GET var passed is "http://localhost/php/po_lines.php?_=1567690257177" The number always changes.
Which caused the SSP to fail, as its not the po_id it was expecting.
I'm fairly certain that this is all due to my lack of under standing this:
data: function ( d ) { d.tk_inv_m1po = rowData.id; }},
From the ajax function.

So, SQL Tables:

Table: tk_inv_m1po
 Columns:
     id 
     m1po_id
     date_created
     date_completed
Table: tk_inv_m1po_lines
Columns:
   id
   po_id (NOTE, this is the id column from tk_inv_m1po.)
   m1part_id
   qty_ordered
   qty_received
   qty_backordered
   date_received
<?php
//Filename po_list.php
include( "../DataTables/lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'tk_inv_m1po', )
    ->fields(
        Field::inst( 'tk_inv_m1po.id' )->set( false ),
        Field::inst( 'tk_inv_m1po.m1po_id' ),
        Field::inst( 'tk_inv_m1po.date_created' ),
        Field::inst( 'tk_inv_m1po.date_complete' )
    )
    ->join(
        Mjoin::inst('tk_inv_m1po_lines')
            ->link('tk_inv_m1po.id', 'tk_inv_m1po_lines.po_id')
            ->fields(
                Field::inst('id')
            )
    )
    //->debug(true)
    ->process( $_POST )
    ->json();
<?php

include( "../DataTables/lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
 
if ( ! isset($_GET['po_id']) || ! is_numeric($_GET['po_id']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'tk_inv_m1po_lines', )
        ->fields(
            Field::inst( 'tk_inv_m1po_lines.po_id' ),
            Field::inst( 'tk_inv_m1po_lines.qty_ordered' ),
            Field::inst( 'tk_inv_m1po_lines.qty_received' ),
            Field::inst( 'tk_inv_m1po_lines.qty_backordered' )
        )
        ->leftJoin( 'tk_inv_m1po', 'tk_inv_m1po.id', '=', 'tk_inv_m1po_lines.po_id' )
        ->where( 'tk_inv_m1po.id', $_GET['po_id'] )
       // ->debug(true)
        ->process( $_GET )
        ->json();
}
<html>
<head>
<link rel="stylesheet" type="text/css" href="../DataTables/datatables.min.css"/>
</head>
<body>
    <div class="container mt-5">
        <table id="poList" class="table table-striped table-bordered" style="width:100%">
            <thead class="thead-dark">
                <tr>
                    <th></th>
                    <th>PO Number</th>
                    <th>Date Created</th>
                    <th>Date Received</th>
                </tr>
            </thead>
            <tfoot class="thead-dark">
                <tr>
                    <th></th>
                    <th>PO Number</th>
                    <th>Date Created</th>
                    <th>Date Received</th>
                </tr>
            </tfoot>
        </table>
    </div>
</body>

<script type="text/javascript" charset="utf8" src="../DataTables/datatables.min.js"></script>

<script>
function createChild ( row ) {
    var rowData = row.data(); 

    var table = $('<table class="display" width="100%"/>');
 
    // Display it the child row
    row.child( table ).show();
    var poLineEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: '../php/po_lines.php',
            data: function ( d ) {
                d.tk_inv_m1po = rowData.id;
            }
        },
        table: table,
        fields: [ {
                label: "M1 PO:",
                name: "tk_inv_m1po_lines.po_id",
                def: rowData.id
            }, {
                label: "M1 Part Number:",
                name: "tk_inv_m1po_lines.m1_id"
            }, {
                label: "Qty Ordered:",
                name: "tk_inv_m1po_lines.qty_ordered"
            }, {
                label: "Qty Received:",
                name: "tk_inv_m1po_lines.received"
            }, {
                label: "Qty Backordered:",
                name: "tk_inv_m1po_lines.qty_backordered"
            }
        ]
    } );

    var poLineTable = table.DataTable( {
        dom: 'Bfrtip',
        pageLength: 5,
        ajax: {
            url: '../php/po_lines.php',
            type: 'get',
            data: function ( d ) {
                d.tk_inv_m1po = rowData.id;
            }
        },
        columns: [
            { title: 'PO', data: 'tk_inv_m1po_lines.po_id' },
            { title: 'M1 Part #:', data: 'tk_inv_m1po_lines.m1_id' },
            { title: 'Qty Ordered', data: 'tk_inv_m1po_lines.qty_ordered' },
            { title: 'Qty Recieved', data: 'tk_inv_m1po_lines.qty_received' },
            { title: 'Qty Backordered', data: 'tk_inv_m1po_lines.qty_backordered' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: poLineEditor },
            { extend: 'edit',   editor: poLineEditor },
            { extend: 'remove', editor: poLineEditor }
        ]
    } );

    poLineEditor.on( 'submitSuccess', function (e, json, data, action) {
        row.ajax.reload(function () {
            $(row.cell( row.po_id(true), 0 ).node()).click();
        });
    } );
}
function updateChild ( row ) {
    $('table', row.child()).DataTable().ajax.reload();
}
function destroyChild(row) {
    var table = $("table", row.child());
    table.detach();
    table.DataTable().destroy();
 
    // And then hide the row
    row.child.hide();
}

$(document).ready(function() {
   var  poLineEditor = new $.fn.dataTable.Editor( {
        ajax: "../php/po_list.php",
        table: "#poList",
        fields: [ {
                label: "PO Number:",
                name: "tk_inv_m1po.m1po_id"
            }, {
                label: "Date Created:",
                name: "tk_inv_m1po.date_created",
                type: "datetime"
            }, {
                label: "Date Received:",
                name: "tk_inv_m1po.date_complete",
                type: "datetime"
            }
        ]
    } );
 
var poLineTable = $('#poList').DataTable( {
        dom: 'Bfrtip',
        order: [ 1, 'asc' ],
        ajax: '../php/po_list.php',
        columns: [
            {
                className: 'details-control',
                orderable: false,
                data: null,
                defaultContent: '',
                width: '10%'
            },
            { data: 'tk_inv_m1po.m1po_id'},
            { data: 'tk_inv_m1po.date_created'},
            { data: 'tk_inv_m1po.date_complete'}
        ],
        select: {
            style:    'os',
            selector: 'td:not(:first-child)'
        },
        buttons: [
            { extend: "create", editor: poLineEditor },
            { extend: "edit",   editor: poLineEditor },
            { extend: "remove", editor: poLineEditor }
        ]
    } );

    $('#poList tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = poLineTable.row( tr );
    
        if ( row.child.isShown() ) {
            // This row is already open - close it
            destroyChild(row);
            tr.removeClass('shown');
        }
        else {
            // Open this row
            createChild(row); // class is for background colour
            tr.addClass('shown');
        }
    } );

    poLineEditor.on('submitSuccess', function () {
        poLineTable.rows().every(function () {
            if (this.child.isShown()) {
                updateChild(this);
            }
        });
    });
});
</script>
</html>

I hope another set of (experienced) eyes will look at this and say "ah you missed this, there" As it has been driving me up the wall for hours.

Thanks for your help!

Rick

Answers

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    Hi Rick,

    _=1567690257177" The number always changes.

    That's jQuery's anti-cache parameter. Its the current time stamp.

    I think the issue is with d.tk_inv_m1po = rowData.id;. I think you actually want:

    d.tk_inv_m1po = rowData.tk_inv_m1po.id;
    

    It would be worth adding console.log(rowData) just before that line so you can double check the structure of the data.

    One other thing, you've got a bit of mix of GET and POST types. Editor will use a POST by default, while DataTables uses a GET by default. For this, I'd suggest using POST for both similar to the blog post.

    Allan

  • rslonerslone Posts: 3Questions: 1Answers: 0

    Switched it all to POST and tried the change, but no joy. console.log(rowData) returns Undefined. I'm going to replicate the full tutorial on my system this weekend and make sure i can do it, before I try a harder database. Thanks for the help and if I figure it out. I'll make a follow up post here.

    Rick

  • rslonerslone Posts: 3Questions: 1Answers: 0

    Got it, it was with the data function. and what was being passed to po_lines.php I changed the createChild() function like so.

    var poLineEditor= new $.fn.dataTable.Editor({
            ajax: {
                url: "../php/po_lines.php",
                data: function(d) {
                    d.po_id = rowData.id;
                }
            },
    ...
    var usersTable = table.DataTable({
           dom: "Bfrtip",
        pageLength: 5,
            ajax: {
                url: "../php/po_lines.php",
                type: "post",
                data: function(d) {
                    d.po_id = rowData.id;
                }
            },
    

    So JS d.po_id is passed via post as $_POST['po_id'], which then needed to be put in the right places.

    if ( ! isset($_POST['po_id']) || ! is_numeric($_POST['po_id']) ) {
        echo json_encode( [ "data" => [] ] );
    }
    else {
        Editor::inst( $db, 'tk_inv_m1po_lines' )
            ->field(
                Field::inst( 'tk_inv_m1po_lines.m1_id' ),
                Field::inst( 'tk_inv_m1po_lines.qty_ordered' ),
                Field::inst( 'tk_inv_m1po_lines.qty_received' ),
                /* Field::inst( 'tk_inv_m1po_lines.po_id' )
                    ->options( 'tk_inv_m1po', 'id', 'm1po_id' )
                    ->validator( 'Validate::dbValues' ), */
                Field::inst( 'tk_inv_m1po_lines.qty_backordered' )
            )
            ->leftJoin( 'tk_inv_m1po', 'tk_inv_m1po.id', '=', 'tk_inv_m1po_lines.po_id' )
            ->where( 'tk_inv_m1po.id', $_POST['po_id'] )
            ->process($_POST)
            ->json();
    }
    

    Going through the Blog tutorial and getting that working first and then modifying it to my situation was a great way to find the errors. It would be nice if it was in the examples area, with all the code listed.

    I hope this helps someone in the future.

    Cheers,

    Rick

This discussion has been closed.