Parent Child Files.

Riddley_Walker · April 2019

Is there any chance anyone has a working set of files or even a fiddle that shows a working parent child set of 2 tables (or even the mixed in version shown in the 2019 parent-child blog post - https://datatables.net/blog/2019-01-11). I can't get it to work and am just about ready after trying for over 20 hours to move on.

colin · April 2019

Hi @Riddley_Walker ,

That example you linked to is using Editor - is that what you're after? If you just want view-only tables, then here's another example on this thread .

Alternatively, link to your page or create an example and we can take a look!

Cheers,

Colin

Riddley_Walker · April 2019

Thanks, yes...I will need editor.

First I tried just making sure I could link up to the databases. I created a page with two tables with editing. It works fine: http://74.124.214.194/~themoviechef/TwoTables.html.

Things seem to break when I work on the .js files. I also wonder if the early article on master client is still current in terms of methodology.

I think I posted everything earlier as a question, but it doesn't seem to be showing up. I will go through it again this evening and post here. I would really appreciate any help!

kthorngren · April 2019

One problem is that you are using the same variables (editor, table) for both site and user Editor and Datatable. You will want different variables. For example, see the change in variables for the user table:

    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.sites.php',
        table: '#sites',
        fields: [
            {
                "label": "name:",
                "name": "name"
            }
        ]
    } );

    var table = $('#sites').DataTable( {
        ajax: 'php/table.sites.php',
        columns: [
            {
                "data": "name"
            }
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );





    var userEditor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.users.php',
        table: '#users',
        fields: [
            {
                "label": "name:",
                "name": "first_name"
            }
        ]
    } );

    var userTable = $('#users').DataTable( {
        ajax: 'php/table.users.php',
        columns: [
            {
                "data": "first_name"
            }
        ],
        select: true,
        lengthChange: false
    } );

    // Note the use of userEditor and userTable variables
    new $.fn.dataTable.Buttons( userTable, [
        { extend: "create", editor: userEditor },
        { extend: "edit",   editor: userEditor },
        { extend: "remove", editor: userEditor }
    ] );

    userTable.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', userTable.table().container() ) );
} );

Kevin

Riddley_Walker · April 2019

Oh dear. It looks like my long response to your email may have gotten swallowed by the ether. I'm going to redo it. Ugh. I will break it into several replies in case there is a length limit.

Riddley_Walker · April 2019

i am lost. my posts don't seem to be showing up if they have code even when i use the triple black ticks. Maybe you're seeing them behind the scenes.

Riddley_Walker · April 2019

OK guys. First, thank you.

It seems like everything I'm doing is making it worse, so I'm going to post what I have so far. But, let me start w/a comment and a question.

Just to recap: the working version of the program with two non-linked tables is at http://74.124.214.194/~themoviechef/TwoTables.html

The Non-working linked-table version is here: http://74.124.214.194/~themoviechef/TwoTables2.html

Kevin, in response to your letter, in my non-working linked version, the editors are, I believe, named differently.

The only thing I'm wondering about is that I'm not seeing a call to jquery, even though the original model I used (from generator) did say to include it. Maybe it is somewhere internally called, but I'm not sure. That said, the unlinked two table version seems to work, so I don't think that is the problem.

Here are the non-working linked-table files. First, the HTML file TwoTables2.html:

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>DataTables Editor - users</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        
        <script type="text/javascript" charset="utf-8" src="js/table.sites2.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>users</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="sites" width="100%">
                <thead>
                    <tr>
                        <th>name</th>
                    </tr>
                </thead>
            </table>
            
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="users" width="100%">
                <thead>
                    <tr>
                        <th></th>
                    </tr>
                </thead>
            </table>

        </div>
    </body>
</html>

Next the js file. Note, I've tried a few versions of this, but ultimately copied this out of the blog post, and modified the location and names of the php files:

$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: "php/table.sites2.php",
        table: "#sites",
        fields: [ {
                label: "Site name:",
                name: "name"
            }
        ]
    } );

    var siteTable = $('#sites').DataTable( {
        dom: "Bfrtip",
        ajax: "php/table.sites2.php",
        columns: [
            { data: 'name' },
            { data: 'users', render: function ( data ) {
                return data.length;
            } }
        ],
        select: {
            style: 'single'
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit",   editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    } );


    var usersEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'php/table.users2.php',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        table: '#users',
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location"
            }
        ]
    } );

    var usersTable = $('#users').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: 'php/table.users2.php',
            type: 'post',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        columns: [
            { data: 'users.first_name' },
            { data: 'users.last_name' },
            { data: 'users.phone' },
            { data: 'sites.name' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: usersEditor },
            { extend: 'edit',   editor: usersEditor },
            { extend: 'remove', editor: usersEditor }
        ]
    } );
    

    siteTable.on( 'select', function () {
        usersTable.ajax.reload();

        usersEditor
            .field( 'users.site' )
            .def( siteTable.row( { selected: true } ).data().id );
    } );

    siteTable.on( 'deselect', function () {
        usersTable.ajax.reload();
    } );
    
    usersEditor.on( 'submitSuccess', function () {
        siteTable.ajax.reload();
    } );

    siteEditor.on( 'submitSuccess', function () {
        usersTable.ajax.reload();
    } );
} );

Then I have the .php file table.sites2.php

<?php

/*
 * Editor server script for DB table sites
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `sites` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'id' )->set( false ),
        Field::inst( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();```

and table.users2.php

<?php

/*
 * Editor server script for DB table users
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `users` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'users' )
        ->field(
            Field::inst( 'users.first_name' ),
            Field::inst( 'users.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}
?>

Thank you for your input and time.

Riddley_Walker · April 2019

First, thanks for your help. I seem to have now broken the non-linked version, too. I don't really care about that one at the moment though. I now feel like a dog chasing its tail.

Here is the HTML File:

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>DataTables Editor - users</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        
        <script type="text/javascript" charset="utf-8" src="js/table.sites2.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>users</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="sites" width="100%">
                <thead>
                    <tr>
                        <th>name</th>
                    </tr>
                </thead>
            </table>
            
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="users" width="100%">
                <thead>
                    <tr>
                        <th>First Name</th><th>Last Name</th><th>Phone</th><th>Site</th>
                    </tr>
                </thead>
            </table>

        </div>
    </body>
</html>

The .js file was largely taken from the internals of the blog post, just modifying the paths and file names

$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: "php/table.sites2.php",
        table: "#sites",
        fields: [ {
                label: "Site name:",
                name: "name"
            }
        ]
    } );

    var siteTable = $('#sites').DataTable( {
        dom: "Bfrtip",
        ajax: "php/table.sites2.php",
        columns: [
            { data: 'name' },
            { data: 'users', render: function ( data ) {
                return data.length;
            } }
        ],
        select: {
            style: 'single'
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit",   editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    } );


    var usersEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'php/table.users2.php',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        table: '#users',
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location"
            }
        ]
    } );

    var usersTable = $('#users').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: 'php/table.users2.php',
            type: 'post',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        columns: [
            { data: 'users.first_name' },
            { data: 'users.last_name' },
            { data: 'users.phone' },
            { data: 'sites.name' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: usersEditor },
            { extend: 'edit',   editor: usersEditor },
            { extend: 'remove', editor: usersEditor }
        ]
    } );
    

    siteTable.on( 'select', function () {
        usersTable.ajax.reload();

        usersEditor
            .field( 'users.site' )
            .def( siteTable.row( { selected: true } ).data().id );
    } );

    siteTable.on( 'deselect', function () {
        usersTable.ajax.reload();
    } );
    
    usersEditor.on( 'submitSuccess', function () {
        siteTable.ajax.reload();
    } );

    siteEditor.on( 'submitSuccess', function () {
        usersTable.ajax.reload();
    } );
} );

I will finish up the php files in the next post.

Riddley_Walker · April 2019

OK guys. First, thank you.

It seems like everything I'm doing is making it worse, so I'm going to post what I have so far. But, let me start w/a comment and a question.

Just to recap: the working version of the program with two non-linked tables is at http://74.124.214.194/~themoviechef/TwoTables.html

The Non-working linked-table version is here: http://74.124.214.194/~themoviechef/TwoTables2.html

Kevin, in response to your letter, in my non-working linked version, the editors are, I believe, named differently.

The only thing I'm wondering about is that I'm not seeing a call to jquery, even though the original model I used (from generator) did say to include it. Maybe it is somewhere internally called, but I'm not sure. That said, the unlinked two table version seems to work, so I don't think that is the problem.

Here are the non-working linked-table files. First, the HTML file TwoTables2.html:

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>DataTables Editor - users</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        
        <script type="text/javascript" charset="utf-8" src="js/table.sites2.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>users</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="sites" width="100%">
                <thead>
                    <tr>
                        <th>name</th>
                    </tr>
                </thead>
            </table>
            
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="users" width="100%">
                <thead>
                    <tr>
                        <th></th>
                    </tr>
                </thead>
            </table>

        </div>
    </body>
</html>

Next the js file. Note, I've tried a few versions of this, but ultimately copied this out of the blog post, and modified the location and names of the php files:

$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: "php/table.sites2.php",
        table: "#sites",
        fields: [ {
                label: "Site name:",
                name: "name"
            }
        ]
    } );

    var siteTable = $('#sites').DataTable( {
        dom: "Bfrtip",
        ajax: "php/table.sites2.php",
        columns: [
            { data: 'name' },
            { data: 'users', render: function ( data ) {
                return data.length;
            } }
        ],
        select: {
            style: 'single'
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit",   editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    } );


    var usersEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'php/table.users2.php',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        table: '#users',
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location"
            }
        ]
    } );

    var usersTable = $('#users').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: 'php/table.users2.php',
            type: 'post',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        columns: [
            { data: 'users.first_name' },
            { data: 'users.last_name' },
            { data: 'users.phone' },
            { data: 'sites.name' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: usersEditor },
            { extend: 'edit',   editor: usersEditor },
            { extend: 'remove', editor: usersEditor }
        ]
    } );
    

    siteTable.on( 'select', function () {
        usersTable.ajax.reload();

        usersEditor
            .field( 'users.site' )
            .def( siteTable.row( { selected: true } ).data().id );
    } );

    siteTable.on( 'deselect', function () {
        usersTable.ajax.reload();
    } );
    
    usersEditor.on( 'submitSuccess', function () {
        siteTable.ajax.reload();
    } );

    siteEditor.on( 'submitSuccess', function () {
        usersTable.ajax.reload();
    } );
} );

Then I have the .php file table.sites2.php

<?php

/*
 * Editor server script for DB table sites
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `sites` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'id' )->set( false ),
        Field::inst( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();```

and table.users2.php

<?php

/*
 * Editor server script for DB table users
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `users` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst( $db, 'users' )
        ->field(
            Field::inst( 'users.first_name' ),
            Field::inst( 'users.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}
?>

Thank you for your input and time.

Riddley_Walker · April 2019

First, thanks for your help. I seem to have now broken the non-linked version, too. I don't really care about that one at the moment though. I now feel like a dog chasing its tail.

Here is the HTML File:

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>DataTables Editor - users</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.18/af-2.3.3/b-1.5.6/b-colvis-1.5.6/b-html5-1.5.6/b-print-1.5.6/cr-1.5.0/fc-3.2.5/fh-3.1.4/kt-2.5.0/r-2.2.2/rg-1.1.0/rr-1.2.4/sc-2.0.0/sl-1.3.0/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        
        <script type="text/javascript" charset="utf-8" src="js/table.sites2.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>users</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="sites" width="100%">
                <thead>
                    <tr>
                        <th>name</th>
                    </tr>
                </thead>
            </table>
            
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="users" width="100%">
                <thead>
                    <tr>
                        <th>First Name</th><th>Last Name</th><th>Phone</th><th>Site</th>
                    </tr>
                </thead>
            </table>

        </div>
    </body>
</html>

The .js file was largely taken from the internals of the blog post, just modifying the paths and file names

$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor( {
        ajax: "php/table.sites2.php",
        table: "#sites",
        fields: [ {
                label: "Site name:",
                name: "name"
            }
        ]
    } );

    var siteTable = $('#sites').DataTable( {
        dom: "Bfrtip",
        ajax: "php/table.sites2.php",
        columns: [
            { data: 'name' },
            { data: 'users', render: function ( data ) {
                return data.length;
            } }
        ],
        select: {
            style: 'single'
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit",   editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    } );


    var usersEditor = new $.fn.dataTable.Editor( {
        ajax: {
            url: 'php/table.users2.php',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        table: '#users',
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location"
            }
        ]
    } );

    var usersTable = $('#users').DataTable( {
        dom: 'Bfrtip',
        ajax: {
            url: 'php/table.users2.php',
            type: 'post',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
        columns: [
            { data: 'users.first_name' },
            { data: 'users.last_name' },
            { data: 'users.phone' },
            { data: 'sites.name' }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: usersEditor },
            { extend: 'edit',   editor: usersEditor },
            { extend: 'remove', editor: usersEditor }
        ]
    } );
    

    siteTable.on( 'select', function () {
        usersTable.ajax.reload();

        usersEditor
            .field( 'users.site' )
            .def( siteTable.row( { selected: true } ).data().id );
    } );

    siteTable.on( 'deselect', function () {
        usersTable.ajax.reload();
    } );
    
    usersEditor.on( 'submitSuccess', function () {
        siteTable.ajax.reload();
    } );

    siteEditor.on( 'submitSuccess', function () {
        usersTable.ajax.reload();
    } );
} );

I will finish up the php files in the next post.

colin · April 2019

Hi @Riddley_Walker ,

Sorry, your replies got caught in the spam filter for some reason - I've released them now and will take a nose in a tick.

Cheers,

Colin

allan · April 2019

http://74.124.214.194/~themoviechef/TwoTables2.html

Is showing a Javascript error. That appears to be because the HTML defines only one column, but the Javascript is defining two for the #sites table. If you add another column to the HTML that should at least initialise.

Likewise with the users table you have one column in the HTML but four defined in the Javascript.

Allan

Parent Child Files.

Parent Child Files.

Replies

Howdy, Stranger!

Categories

DataTables

Parent Child Files.

Parent Child Files.

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables