Working with a selected subset of a table

TomBajzek

Ive been happily using DataTables/Editor for a couple of months now, but I'm still learning, and I've come across a couple of things that I apparently don't understand correctly:

How can I load a subset of a database from the server and then operate on that same subset of data using AJAX? I viewed this as using server-side processing to load the table subset, and then operating on it as though it were DOM-sourced.

My PHP code begins by attempting to load the desired subset of the data using PHP, after which I'd thought I could just operate on it using AJAX. What I find is that the data initially loaded consists of the entire table, not just the subset defined in my MySQL select statement.

I do not need or want to do a server-side reload of the page for every AJAX update, provided that I can just continue working with the initial subset. Am I misunderstanding what I can do here, or am I misunderstanding how to do it?

As an aside, how would I use DOM-sourced data without loading it into the DOM by loading with with a SQL select statement?

Thanks very much

allan

Hi,

How can I load a subset of a database from the server and then operate on that same subset of data using AJAX?

What is the sub-set? It is a single page of data? If so, then yes, server-side processing sounds like it would be exactly what is required. What PHP script are you using to implement server-side processing if so - your own, the SSP class or something else?

However, as you then say, you don't really want an Ajax request for every page, which is fundamentally what server-side processing is all about. So I'd need a little clarification on what it is your are trying to do please.

As an aside, how would I use DOM-sourced data without loading it into the DOM by loading with with a SQL select statement?

You can't use DOM sourced data without loading it into the DOM. So the question then becomes, are you Ajax loading this data (in which case use rows.add()) or are you DOM loading it and need to limit the data (in which case add a WHERE condition to however you are currently reading the data out of the database).

Allan

TomBajzek

Allan,

I'm still learning here, and a lot of this is stuff I'm doing for the first time, so I appreciate your patience and help.

I'd made a PHP program that loads data from the DB into the table to which the DataTable definition and Editor definition are attached. This program selects a subset of the DB via a WHERE clause. In their ajax calls, the DataTable and Editor invoke a different PHP program, which has been lacking the WHERE clause, which I'd thought unnecessary for updating and redisplaying a record. What I see when I load the page is that the entire DB table has been loaded, not the subset defined by the original PHP program. This tells me that an ajax load is happening when the page loads, leading me to ask how one would configure a DOM-sourced table that would be updated via ajax. In the online example, the DataTable definition does not have the ajax call; when I do it this way, I see the subset of the DB table that the "original" PHP program loads, as restricted by its WHERE clause; however, I cannot edit the, as the Edit button causes no action with existing records. When I leave the ajax call in the DataTable definition, the entire table is loaded over the desired subset.

The subset of data that is loaded is determined by the user's role in the system (admin, or ordinary user), and some user-configurable parameters (such as the time since update, etc.), from which the WHERE-clause for loading the data is derived. I had assumed that the PHP used by the ajax call would just update and refresh the edited records, and not load the entire table, and the the "original" PHP program would load then entire table.

There is something conceptual here, regarding DOM-sourced vs Ajax-sourced, and the Ajax updating that I'm not getting. This is where some words of clarification would help. Meanwhile, I'm going to resume studying the difference between DOM-sourced and ajax-sourced, to try to figure out what I've been missing.

Thanks,
Tom

rf1234

Hi Tom,
sounds a bit complicated but maybe this helps ... You could do everything in one go: Load a subset of your data; make sure that only authorized users can see and manipulate the data. This example is from my own coding. It is my user table query in PHP. Depending on authorization levels etc. you can either only edit your own settings or (in case you are administrator or principal) you can also edit the settings of other users. The same PHP function supplies the data for three pages: settings (i.e. your own settings) and master data (i.e. among other things the administration of your department's users) plus another administration page for something called 'lgf'.

function tblUser(&$db, &$lang) {
    if ($lang === 'de') {     
        $msg[0] = 'Feld darf nicht leer sein.';
        $msg[1] = 'Bitte geben Sie eine gültige E-Mail Adresse ein.';      
    } else {
        $msg[0] = 'Field may not be empty.';
        $msg[1] = 'Please enter a valid email address.'; 
    }
    $currentUser = filter_var($_SESSION['id']);
    
    Editor::inst( $db, 'user' )
    ->field(
        Field::inst( 'user.id' )->set( false ),
        Field::inst( 'user.title' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.acad' ),
        Field::inst( 'user.firstname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.lastname' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.language' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.type' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'user.email' )->validator( 'Validate::email', array(
                                                'required' => true,
                                                'message' => $msg[1]) ),
        Field::inst( 'user.updater_id' )->set(Field::SET_BOTH)
                                        ->setValue($currentUser),
        Field::inst( 'user.creator_id' )->set(Field::SET_CREATE)
                                        ->setValue($currentUser)    
    )
//    show user roles        
    ->join(
        Mjoin::inst( 'govdept' )
            ->link( 'user.id', 'govdept_has_user.user_id' )
            ->link( 'govdept.id', 'govdept_has_user.govdept_id' )
            ->order( 'govdept.name asc' )
            ->fields(
                Field::inst( 'govdept.name AS deptName' )->set( false ),
                Field::inst( 'govdept_has_user.role AS userRole' )->set( false )
            )
        )
    ->join(
        Mjoin::inst( 'creditor' )
            ->link( 'user.id', 'creditor_has_user.user_id' )
            ->link( 'creditor.id', 'creditor_has_user.creditor_id' )
            ->order( 'creditor.name asc' )
            ->fields(
                Field::inst( 'creditor.name AS deptName' )->set( false ),
                Field::inst( 'creditor_has_user.role AS userRole' )->set( false )
            )
        )
    ->join(
        Mjoin::inst( 'lgf' )
            ->link( 'user.id', 'lgf.user_id' )
            ->fields(
                Field::inst( 'lgf.role AS userRole' )->set( false )
            )
        )
    ->where( function ( $q ) {
            if ( isset($_SESSION['settingsId']) ) {
                $idLower = $_SESSION['settingsId'];
                $idHigher = $_SESSION['settingsId'];
            } else {
                $idLower = 0;
                $idHigher = 999999999999999999999;
            }
            if (! isset($_SESSION['masterDataId']) ) {
                $q  ->where( 'user.id', $idLower, '>=' );
                $q  ->where( 'user.id', $idHigher, '<=' );
            } else {
                $q  ->where('user.id',  $_SESSION['masterDataId'], '!=' ); 
                $q  ->where( function ( $r ) { 
                    $r  ->where( 'user.id',  
                        '( SELECT DISTINCT user.id  
                            FROM user, govdept_has_user  
                            WHERE govdept_has_user.govdept_id IN  
                            ( SELECT DISTINCT govdept_id FROM govdept_has_user     
                              WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                            govdept_has_user.user_id = user.id  
                            ORDER BY user.id ASC  
                            )', 'IN', false);                
                    $r  ->or_where( 'user.id',  
                        '( SELECT DISTINCT user.id  
                            FROM user, creditor_has_user  
                            WHERE creditor_has_user.creditor_id IN   
                            ( SELECT DISTINCT creditor_id FROM creditor_has_user     
                              WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
                            creditor_has_user.user_id = user.id  
                            ORDER BY user.id ASC  
                            )', 'IN', false);
                    $r  ->or_where( 'user.id',  
                        '( SELECT DISTINCT user.id  
                            FROM user, lgf  
                            WHERE user.id IN   
                            ( SELECT DISTINCT user_id FROM lgf ) AND   
                            lgf.user_id = :id   AND    
                            lgf.role IN ("Administrator", "Principal" )  
                            ORDER BY user.id ASC  
                            )', 'IN', false);
                    //you can also see users that you have created and/or updated last time
                    $r  ->or_where( 'user.updater_id', $_SESSION['masterDataId'], '=' );
                    $r  ->bind( ':id', $_SESSION['masterDataId'] );
                } );
            }
        } )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'create', $id, $values, 'user' );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $values, 'user' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'user' );
    } )
    ->process($_POST)
    ->json();
}

allan

Tom: I presume you are referring to this example - a DOM sourced table which has been created by the server-side, and is then updated by Editor?

Editor should update the table for you when you submit data (create, edit or delete). Is that what you mean by refreshing the table?

Are you using any other DataTables API methods? Showing your code would be really useful.

Thanks,
Allan

TomBajzek

Allan,

The response from rf1234 is helping via his example, and I want to continue working with what I'm learning from this before sending a code sample, as that might not be necessary.

The conceptual issue I referred to is the contrast between the example in Basic Initialization and the example of DOM-Sourced data. The Basic Initialization example shows a PHP call (../php/staff.php) in both the Editor and the DataTable definitions. The example of DOM-Sourced data has a PHP call (../php/staff-html.php) only in the Editor definition, presumably because the data is loaded into the DOM by database activity outside the scope of the code shown in the example. That is what I attempted to do. Originally, I had a Ajax-PHP call configured in both the Editor and the DataTable (which I see is wrong), and the effect was that the Ajax code overwrote the data I had loaded into the DOM by PHP/Mysql when I initialized the table. (I know this because my code to load the DOM selected only a portion of the data and ordered it differently than the Ajax-PHP call does it. In this case, operations on the (incorrectly selected) data worked correctly. When I removed the Ajax-PHP from the DataTable configuration, per the example for DOM-sourced data, I then had the selection of data in the order I wanted, but I could not Edit or Delete anything I selected in the table that was loaded. This is what I couldn't understand. I still don't understand this, but I'm learning from rf1234's example right now, and may figure this out.

In any case, I expect to have a simpler and clearer code example after I do this, which I'll send if I stilled help with this then. Because of the sort and filter capabilities of DataTables that my original implementation lacked, the original code is much larger anymore complicated, and an unreasonable burden to hand to someone who might be kind enough to help.

I'll let you know how this develops for me. Meanwhile, thanks for being responsive to my issues.

Thanks,
Tom

rf1234

Tom,
To be honest I have never really looked at the DOM sourced data option because for me it had been clear from the beginning that I would either use Editor for data selection and manipulation or I would use my own SQL statements and try to get them into the data tables front end via Ajax. That would've only applied to more complex reporting not involving data manipulation. I think I found a way to even avoid this and hence avoid to have to deal with the intricacies of sending the right data format to the data tables front end. Stuff like “dt_rowid“ that somehow has to be in the Ajax was kind of turning me off ...
What I plan to do is to have individual SQL statements provide the input for the various fields of my more complex reports, feed this all into editor on the back end side through “get formatters“ etc and have it sent to the front end data table through the established communication between Editor and the data tables front end.

allan

presumably because the data is loaded into the DOM by database activity outside the scope of the code shown in the example.

Actually - its in the HTML for the page. If you right click and View source you'll be able to see it. The data for the initial table view is not being loaded by Ajax in that example (it is in all the other examples!).

but I could not Edit or Delete anything I selected in the table that was loaded. This is what I couldn't understand.

I'd need to be able to see the page to understand why not. My guess is that the rows didn't have an id.

Allan

TomBajzek

Allan,

(I've finally gotten back to this project.) You were right about the inability to edit, in that I'd omitted the id for the rows. I put that in, and editing is now possible.

Thanks,
Tom

TomBajzek

Based on rf1234's example, I've made a PHP file (newEditor.php) that tries to load my data into the DOM, based on user data that I've successfully loaded from other (non-DataTables) database tables.

Once my DataTable has been loaded, I expect another PHP file, defined in the Editor definition and which I've already tested, to process the Ajax calls required. What's missing is (at least) the relationship between the new file that is supposed to load my Datatable and the table it is supposed to load.

When I invoke the URL of the new program (newEditor.php) I see the page HTML such as the table header (which is included in newEditor.php) with an empty data table, followed by the string:

{"data":[],"options":[],"files":[]}

I'm not sure what this is telling me. Am I getting no data from the server? Am I getting data that is not ending up in the table? Is my problem in newEditor.php, or elsewhere?

My newEditor.php file is below.

Thanks for looking at this,
Tom

======================

<?php
include 'editorPage-html.php';


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

// DataTables PHP library and database connection
include( "php/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;


//
// Now do the setup needed to determine the user, role, and preferences, and to populate
//  categories and itResponder tables

//$theUser= $_SERVER['REMOTE_USER'];
$theUser = 'tom';                       // Kludge for running in MAMP


// Connect to the userAttributes DB to access the user's role and preferences
include './uaInclude.php';

// Now get the itRole from the UserAttributes data for the user
$userQry = $ua->prepare('select itRole from roles where email = ?');
$userQry->execute([$theUser]);
$itRole = $userQry->fetchColumn();

// Here we get the itMaxAge for the user
$prefQry = $ua->prepare('select itMaxAge from preferences where email = ?');
$prefQry->execute([$theUser]);
$maxAge = $prefQry->fetchColumn();





// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tickets', 'id' )
    ->fields(
        Field::inst( 'id' )->set(false),
        Field::inst( 'tickettime' )
            ->getFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime( $val) );
                } ),
        Field::inst( 'updated' )
            ->setValue(date("Y-m-d H:i:s"))
            ->getFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime( $val) );
                } )
            ->setFormatter( function( $val, $data, $opts) {
                return date("Y-m-d H:i:s", strtotime($val));
                } ),
        Field::inst( 'description' ),
        Field::inst( 'category' ),
        Field::inst( 'email' ),
        Field::inst( 'status' ),
        Field::inst( 'device' ),
        Field::inst( 'devstatus' ),
        Field::inst( 'severity' ),
        Field::inst( 'responder' )
    )
    ->where( function ($q) {
        global $itRole,$maxAge,$theUser;
        if ($itRole != 'tech' and $itRole != 'admin') {
            $q ->where('email',$theUser,'=');   // Restrict non-(tech,admin) to own tickets
        }
        if ($maxAge != 'all') {
            $q ->where('updated','date_sub(now(),interval ' . $maxAge . ')','>');
        }
    })
//  ->leftJoin('notes','notes.ticket_id','=','tickets.id')
    ->process( $_POST )
    ->json();

allan

{"data":[],"options":[],"files":[]}

It suggests that there is nothing meeting the where condition.

I would suggest adding ->debug( true ) before the -process() call which will let you see the SQL being expected.

Allan

TomBajzek

The output with the debug method in the code is:

{
    "data": [],
    "options": [],
    "files": [],
    "debugSql": [{
        "query": "SELECT `id` as 'id', `tickettime` as 'tickettime', `updated` as 'updated', `description` as 'description', `category` as 'category', `email` as 'email', `status` as 'status', `device` as 'device', `devstatus` as 'devstatus', `severity` as 'severity', `responder` as 'responder' FROM `tickets` WHERE `updated` > :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "date_sub(now(),interval 1 year)",
            "type": null
        }]
    }]
}

When I run this query in my MySQL utility with the value of the where_0 binding plugged-in, I get 408 rows data, which appears to be correct.

I'm still puzzled.

allan

Ah - its treating the value as a string. You need to tell it to not bind the value:

$q ->where('updated','date_sub(now(),interval ' . $maxAge . ')','>', false);

the false means that the value basically won't be quoted.

Allan

allan

One other thing, might be worth reading over this part of the docs which describes how to bind values to make sure you won't suffer from SQL injection!

Allan

rf1234

there is an example for binding in my code above and in the attached code below. In case you need to join tables in the where clause of an options instance where ->leftJoin does not work you would also need to use the "false" parameter like this to avoid the second field to be quoted. Same applies in case you want to use subselects.

Field::inst( 'rfp_has_creditor.creditor_id' )
    ->options( Options::inst()
        ->table('creditor, govprefcreditor')
        ->value('creditor.id')
        ->label( array('creditor.name', 'creditor.BIC', 'creditor.city') )
        ->render( function ( $row ) {
            if ($row['creditor.BIC'] > '') {
                return $row['creditor.name'].' (BIC: '.$row['creditor.BIC'].'), '
                    .$row['creditor.city'];
            } else {
                return $row['creditor.name'].', '.$row['creditor.city'];
            }
        } )
        ->order( 'creditor.name asc' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            $q ->where( function($r) {
                $r ->where('creditor.is_client', 1 );
                $r ->or_where( function($s) {
                        $s ->where('govprefcreditor.creditor_id', 'creditor.id', '=', false); //join
                        $s ->where('govprefcreditor.gov_id', $_SESSION['rfp_gov_id']);
                });
            });
            $q ->where('creditor.id',
                '( SELECT DISTINCT rfp_has_creditor.creditor_id  
                     FROM rfp_has_creditor  
                    WHERE rfp_has_creditor.rfp_id = :rfp_id
                )', 'NOT IN', false);        
            $q ->bind( ':rfp_id', $_POST['rfp_id'] );
        } )
    )

TomBajzek

I appreciate your suggestions about binding query parameters. I'm aware of that and will get to it in due time.

Meanwhile, setting the binding parameter for maxAge in the WHERE-clause to false, as you have suggested, has partially fixed the problem, in that I now get the entire expected response. There is a remaining problem, which is that the query result delivered is displayed as JSON, after an empty ticketTable.

So, it appears that my code is not processing the JSON that the PHP listed above delivers. My goal was to have that PHP populate the table in the DOM. I've modeled my code on the DOM-Source example. I had Ajax-based code (based on the Basic Initialization example) that worked, and modeled the code I'm working on now on the DOM-Sourced example. I've not been able to see how the differences in my old and new code depart from that model.

My mental model is as follows: the Ajax in the Editor just needs to write the edited changes back to the server and reload the elements that have been changed or added. The code that loads the DOM to begin with uses other information to determine what needs to be loaded into the DOM, in what order, etc, and its job is done once the DOM has been loaded with the desired initial data. I believe this is why the php code in the DOM-Sourced example does not have an Ajax-PHP call in its DataTable definition.

Am I right about how this should work, or am I missing something that is the cause of my problem? I've attached the relevant PHP code, and the Javascript is here:

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

(function($){

    $(document).ready(function() {
    
        $('.moreF').more({length: 80, wordBreak: true});
    
        var ticketEditor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.tickets.php',
            table: '#tickets',
            formOptions: {
                main: {
                    submit: 'changed'
                }
            },
            fields: [
                {
                    label: "id",
                    name: "id",
                    type: "hidden"
                },
                {
                    label: "updated:",
                    name: "updated",
                    type: "readonly"
                },
                {
                    label: "status:",
                    name: "status",
                    type: "select",
                    placeholder: "- Select status -",
                    options: [
                        "closed",
                        "holding",
                        "in progress",
                        "in  queue",
                        "unseen",
                        "urgent"
                    ],
                    def: "unseen"
                },
                {
                    label: "responder:",
                    name: "responder",
                    type: "select",
                    placeholder: "- Select responder -",
                    options: [
                        "CZ",
                        "ES",
                        "KD",
                        "LM",
                        "TB"
                    ]
                },
                {
                    label: "originator:",
                    name: "email"
                },
                {
                    label: "category:",
                    name: "category",
                    type: "select",
                    placeholder: "- Select category -",
                    options: [
                        "computer",
                        "software",
                        "disk",
                        "network",
                        "server"
                    ]
                },
                {
                    label: "description:",
                    name: "description",
                    type: "textarea"
                },
                {
                    label: "device:",
                    name: "device",
                    type: "select",
                    placeholder: "- Select device -",
                    options: [
                        "PC",
                        "router",
                        "switch",
                        "printer",
                        "phone",
                        "monitor",
                        "cabling",
                        "server",
                        "software"
                    ]
                },
                {
                    label: "devstatus:",
                    name: "devstatus",
                    type: "select",
                    placeholder: "- Select devStatus -",
                    options: [
                        "usable",
                        " unusable",
                        "intermittent",
                        "unknown"
                    ]
                },
                {
                    label: "severity:",
                    name: "severity",
                    type: "select",
                    placeholder: "- Select severity -",
                    options: [
                        "critical",
                        "normal",
                        "when convenient",
                        "ex-critical"
                    ],
                    def: "normal"
                }
            ]
        } );
    
        var ticketTable = $('#tickets').DataTable( {
            dom: 'Bflrtip',
            order: [[3,"desc"]],
//          ajax: 'php/table.tickets.php',
            columns: [
                {
                    data: "id"
                },
                {
                    data: "tickettime",
                    className: "tStamp"
                },
                {
                    data: "updated",
                    className: "tStamp"
                },
                {
                    data: "status",
                    className: " prose cFilter"
                },
                {
                    data: "responder",
                    className: " prose cFilter"
                },
                {
                    data: "email",
                    className: " prose cFilter"
                },
                {
                    data: "category",
                    className: " prose cFilter"
                },
                {
                    data: "description",
                    className: " moreF"
                }
            ],
            select: {
                style: 'single'
            },
            lengthChange: true,
//          drawCallback: function(settings){
//              $('.moreF').more();
//          },
            buttons: [
                { extend: 'create', editor: ticketEditor },
                { extend: 'edit',   editor: ticketEditor },
                { extend: 'remove', editor: ticketEditor }          
            ],
            
            initComplete: function () {
                this.api().columns('.cFilter').every( function () {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                        .appendTo( $(column.header()) )
                        .on( 'change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val()
                            );
                            column
                                .search( val ? '^'+val+'$' : '', true, false )
                                .draw();
                        } );
                        column
                            .cache( 'search' )
                            .sort()
                            .unique()
                            .each( function ( d ) {
                                select.append( $('<option value="'+d+'">'+d.substr(0,15)+'</option>') );
                            } );
                } );
            }
        } );
        
    

    
        ticketTable.buttons().container()
            .appendTo( $('.col-sm-6:eq(0)', ticketTable.table().container() ) );
    
    
        var noteEditor = new $.fn.dataTable.Editor( {
            ajax: {
                url: 'php/table.notes.php',
                data: function(d) {
                    var selected = ticketTable.row( { selected : true} );
                    if (selected.any()) {
                        d.ticket= selected.data().id;
                    }
                }
            },
            table: '#notes',
            fields: [
    //          {
    //              label: "created:",
    //              name: "created",
    //              type: "datetime",
    //              def: function () { return new Date(); },
    //              format: "YYYY-MM-DD HH:mm:ss"
    //          },
                {
                    label: "ticket #",
                    name: "notes.ticket_id",
                    type: "select",
                    placeholder: "Select a ticket Id"
                    
                },
                {
                    label: "commenter:",
                    name: "notes.email"
                },
                {
                    label: "content:",
                    name: "notes.content",
                    type: "textarea"
                },
                {
                    label: "resolved?:",
                    name: "notes.resolved",
                    type: "select",
                    placeholder: "yes/no",
                    options: [
                        "no",
                        "yes"
                    ],
                    default: "no"
                }
            ]
        } );
    
        var noteTable = $('#notes').DataTable( {
            ajax: {
                url: 'php/table.notes.php',
                dom: 'Brit',
                type: "post",
                data: function(d) {
                    var selected = ticketTable.row( {selected: true} );
                    if (selected.any()) {
                        d.ticket = selected.data().id;
                        $('#noteDiv').show();   // show notes div here
                    } else {
                        $('#noteDiv').hide();   // hide notes div here if none selected
                    }   
                }
            },
            paging: false,
    //      display: "none",
        
            order: [[0,"desc"]],
            columns: [
    //          {
    //              data: "id"
    //          },
                {
                    data: "notes.created",
                    className: "ntStamp",
                    orderable: false
                },
                {
                    data: "tickets.id",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.email",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.resolved",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.content",
                    className:" moreF",
                    orderable: false
                }
            ],
            select: true,
            lengthChange: false
//          buttons: [
//              {
//                  text: "Show notes",
//                  action: {
//                      $('#noteTable').show();
//                  }
//              }
//          ]
        } );
    
        new $.fn.dataTable.Buttons( noteTable, [
            { extend: "create", editor: noteEditor }
//          { text: "Show notes",
//              action: function (e, dt, nore, config) {
//                  $('#noteTable').show();
//              }
//          }
        ] );
    
        noteTable.buttons().container()
            .appendTo( $('.col-sm-6:eq(0)', noteTable.table().container() ) );
    
        ticketTable.on( 'select', function () { 
            noteTable.ajax.reload();
    
            noteEditor
                .field( 'notes.ticket_id' )
                .def( ticketTable.row( { selected: true } ).data().id);
        } );
        
        ticketTable.on( 'deselect', function () {
            noteTable.ajax.reload();
        } );
    
        noteEditor.on( 'submitSuccess', function () {
            ticketTable.ajax.reload();
        } );
    
        ticketEditor.on( 'submitSuccess', function () {
            noteTable.ajax.reload();
        } );
        
//      $('.moreF').more(); 
    } );
}(jQuery));

Thanks for your insights,
Tom

TomBajzek

I found my problem! I had not removed the "false" in the WHERE-clause when I put the leftJoin back into the code.

Sorry for the false-alarm.

Now I can put the bindings into the queries and go from there.

Thanks,
Tom

allan

Super - thanks for posting back. Good to hear you've got it going.

Allan

TomBajzek

For the record, to alleviate your justified anxiety, the variables $theUser and $maxAge are taken from database entries related to the user, but are not entered in a form by the user.

TomBajzek

My report of success yesterday was incorrect. I had accidentally refreshed a page for a different, earlier URL that successfully populated the DOM with data, but using an earlier test query, which is incomplete and non-PDO style, rather then the version I'd edited with to implement the suggestion regarding using false in the WHERE-clause. I was too tired to notice this last night, apparently, when I posted a report of success.

Currently, my PHP program, which populates the DOM, has the leftJoin uncommented, and the WHERE-clause with the 4th parameter set to false. In this case, the PHP produces an empty table, but the debug shows the JSON of the query result (which appears to the correct content that I expect from the query), followed by the text of the actual query.

The problem is that the result is not in the DataTable defined, which is empty. I've tried to model my code on the DOM-sourced example, as contrasted with the Basic Initialization example in the Editor documentation. Apparently, I've missed something, and I can't see what that is.

I've attached the most recent copy of the PHP program, and included the Javascript here, if you would be willing to look at it.

Thanks again,
Tom

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

(function($){

    $(document).ready(function() {
    
//      $('.moreF').more({length: 80, wordBreak: true});
    
        var ticketEditor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.tickets.php',
            table: '#tickets',
            formOptions: {
                main: {
                    submit: 'changed'
                }
            },
            fields: [
                {
                    label: "id",
                    name: "id",
                    type: "hidden"
                },
                {
                    label: "updated:",
                    name: "updated",
                    type: "readonly"
                },
                {
                    label: "status:",
                    name: "status",
                    type: "select",
                    placeholder: "- Select status -",
                    options: [
                        "closed",
                        "holding",
                        "in progress",
                        "in  queue",
                        "unseen",
                        "urgent"
                    ],
                    def: "unseen"
                },
                {
                    label: "responder:",
                    name: "responder",
                    type: "select",
                    placeholder: "- Select responder -",
                    options: [
                        "CZ",
                        "ES",
                        "KD",
                        "LM",
                        "TB"
                    ]
                },
                {
                    label: "originator:",
                    name: "email"
                },
                {
                    label: "category:",
                    name: "category",
                    type: "select",
                    placeholder: "- Select category -",
                    options: [
                        "computer",
                        "software",
                        "disk",
                        "network",
                        "server"
                    ]
                },
                {
                    label: "description:",
                    name: "description",
                    type: "textarea"
                },
                {
                    label: "device:",
                    name: "device",
                    type: "select",
                    placeholder: "- Select device -",
                    options: [
                        "PC",
                        "router",
                        "switch",
                        "printer",
                        "phone",
                        "monitor",
                        "cabling",
                        "server",
                        "software"
                    ]
                },
                {
                    label: "devstatus:",
                    name: "devstatus",
                    type: "select",
                    placeholder: "- Select devStatus -",
                    options: [
                        "usable",
                        " unusable",
                        "intermittent",
                        "unknown"
                    ]
                },
                {
                    label: "severity:",
                    name: "severity",
                    type: "select",
                    placeholder: "- Select severity -",
                    options: [
                        "critical",
                        "normal",
                        "when convenient",
                        "ex-critical"
                    ],
                    def: "normal"
                }
            ]
        } );
    
        var ticketTable = $('#tickets').DataTable( {
            dom: 'Bflrtip',
            order: [[3,"desc"]],
//          ajax: 'php/table.tickets.php',
            columns: [
                {
                    data: "id"
                },
                {
                    data: "tickettime",
                    className: "tStamp"
                },
                {
                    data: "updated",
                    className: "tStamp"
                },
                {
                    data: "status",
                    className: " prose cFilter"
                },
                {
                    data: "responder",
                    className: " prose cFilter"
                },
                {
                    data: "email",
                    className: " prose cFilter"
                },
                {
                    data: "category",
                    className: " prose cFilter"
                },
                {
                    data: "description",
                    className: " moreF"
                }
            ],
            select: {
                style: 'single'
            },
            lengthChange: true,
//          drawCallback: function(settings){
//              $('.moreF').more();
//          },
            buttons: [
                { extend: 'create', editor: ticketEditor },
                { extend: 'edit',   editor: ticketEditor },
                { extend: 'remove', editor: ticketEditor }          
            ],
            
            initComplete: function () {
                this.api().columns('.cFilter').every( function () {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                        .appendTo( $(column.header()) )
                        .on( 'change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val()
                            );
                            column
                                .search( val ? '^'+val+'$' : '', true, false )
                                .draw();
                        } );
                        column
                            .cache( 'search' )
                            .sort()
                            .unique()
                            .each( function ( d ) {
                                select.append( $('<option value="'+d+'">'+d.substr(0,15)+'</option>') );
                            } );
                } );
            }
        } );
        
    

    
        ticketTable.buttons().container()
            .appendTo( $('.col-sm-6:eq(0)', ticketTable.table().container() ) );
    
    
        var noteEditor = new $.fn.dataTable.Editor( {
            ajax: {
                url: 'php/table.notes.php',
                data: function(d) {
                    var selected = ticketTable.row( { selected : true} );
                    if (selected.any()) {
                        d.ticket= selected.data().id;
                    }
                }
            },
            table: '#notes',
            fields: [
    //          {
    //              label: "created:",
    //              name: "created",
    //              type: "datetime",
    //              def: function () { return new Date(); },
    //              format: "YYYY-MM-DD HH:mm:ss"
    //          },
                {
                    label: "ticket #",
                    name: "notes.ticket_id",
                    type: "select",
                    placeholder: "Select a ticket Id"
                    
                },
                {
                    label: "commenter:",
                    name: "notes.email"
                },
                {
                    label: "content:",
                    name: "notes.content",
                    type: "textarea"
                },
                {
                    label: "resolved?:",
                    name: "notes.resolved",
                    type: "select",
                    placeholder: "yes/no",
                    options: [
                        "no",
                        "yes"
                    ],
                    default: "no"
                }
            ]
        } );
    
        var noteTable = $('#notes').DataTable( {
            ajax: {
                url: 'php/table.notes.php',
                dom: 'Brit',
                type: "post",
                data: function(d) {
                    var selected = ticketTable.row( {selected: true} );
                    if (selected.any()) {
                        d.ticket = selected.data().id;
                        $('#noteDiv').show();   // show notes div here
                    } else {
                        $('#noteDiv').hide();   // hide notes div here if none selected
                    }   
                }
            },
            paging: false,
    //      display: "none",
        
            order: [[0,"desc"]],
            columns: [
    //          {
    //              data: "id"
    //          },
                {
                    data: "notes.created",
                    className: "ntStamp",
                    orderable: false
                },
                {
                    data: "tickets.id",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.email",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.resolved",
                    className: "narrow",
                    orderable: false
                },
                {
                    data: "notes.content",
                    className:" moreF",
                    orderable: false
                }
            ],
            select: true,
            lengthChange: false
//          buttons: [
//              {
//                  text: "Show notes",
//                  action: {
//                      $('#noteTable').show();
//                  }
//              }
//          ]
        } );
    
        new $.fn.dataTable.Buttons( noteTable, [
            { extend: "create", editor: noteEditor }
//          { text: "Show notes",
//              action: function (e, dt, nore, config) {
//                  $('#noteTable').show();
//              }
//          }
        ] );
    
        noteTable.buttons().container()
            .appendTo( $('.col-sm-6:eq(0)', noteTable.table().container() ) );
    
        ticketTable.on( 'select', function () { 
            noteTable.ajax.reload();
    
            noteEditor
                .field( 'notes.ticket_id' )
                .def( ticketTable.row( { selected: true } ).data().id);
        } );
        
        ticketTable.on( 'deselect', function () {
            noteTable.ajax.reload();
        } );
    
        noteEditor.on( 'submitSuccess', function () {
            ticketTable.ajax.reload();
        } );
    
        ticketEditor.on( 'submitSuccess', function () {
            noteTable.ajax.reload();
        } );
        
//      $('.moreF').more(); 
    } );
}(jQuery));

allan

That's a lot of code .

Could you show me the JSON returned by the server as well please?

Thanks,
Allan

TomBajzek

Allan,

After sleeping on this, it occurs to me that I've been interpreting the example of DOM-sourced code incorrectly. I think the problem is just that I do not have code to build the DataTable the first time, out of the JSON response that we are discussing. The example code starts with the DOM already populated, but in my case I need to do that. I think I was reasoning falsely that the DataTables framework was going to handle this, as I had defined the table in HTML and Javascript. I believe that is true AFTER the DOM is populated first by the code I have written, but I need to add the code to populate it.

Does that make sense to you? (I ask before sending a pile of JSON.) I'm going to work on this now.

Thanks,
Tom

TomBajzek

If my last post is basically correct, I need to know how to refer to the content of the JSON response in PHP in order to populate the table in the DOM. If I've defined my Editor on the table 'tickets', is that the object I can process, such as

foreach ( $tickets as $row) {
... process row for display;
}

(I'm relatively new to PDO as well as to DataTables/Editor, and I'm not sure about this.)
Thanks,
Tom

allan

Let's start back at the start.

You have a database data source and you want to make it editable. It sounds like you are trying to populate an HTML table with the data when the page is loaded, rather than using Ajax to populate the data. Is that correct? If so, can I ask why you are doing it that way rather than Ajax loading the data?

Allan

TomBajzek

Yes, I have a database data source that I want to make editable. I'm trying to populate it into the DataTable prior to using Ajax because I need to make a selection of data based on the user, his access privileges (based on his role in the system), and possibly other parameters that are not part of the data source to be edited, and that need not be considered once the initial set of data has been selected. My plan was to populate the DOM with the selected data, based on these other criteria. I envisioned that, after that, Ajax updating of the body of data selected would be sufficient.

Tom

allan

Ajax because I need to make a selection of data based on the user, his access privileges

You can do that in the Ajax request handler. Just apply a condition based on the access rights of the user.

Allan

TomBajzek

OK. I'll try that. It's a different line of thinking than I had been on, but I'm willing to try that. I think I've learned enough from this discussion to try that alternative approach.

Thanks for the suggestion
Tom

TomBajzek

Allan,

Using the information that you and RF1234 provided here, I was able to figure out these issues and get what I'm building to work correctly. I actually backed up several steps in my development process to a much simpler, but known-working state, and then I applied what I'd learned from you and from further study of the documentation. Coming forward from that reference point was much simpler and clearer than trying to retrofit your suggestions onto a body of patchwork code, and I was able to do that without bothering you again. (This is a process I'd recommend to others who might have problems absorbing and applying new techniques.)

In any case, I want to that you and rf1234 for your patient and helpful guidance with these issues.

I think Datatables and Editor are excellent tools, and I highly recommend them. I've done several projects over the past five years or so that would be better if I'd known about and used your tools to build them.

Thanks,
Tom