joined tables losses data changes

jonescw · September 2015

when I join 2 tables together, so some reason the changed data is not being passed to the php program.

I can get it to work with out the join but when I had the join the changed data does not get passed

php server script

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;


   Editor::inst( $db) 
   ->table("$dbname.ReportGroup")
   ->pkey('reportGroupID')
   ->fields(
      Field::inst( 'ReportGroup.reportGroupID' ),
      Field::inst( 'ReportGroup.reportGroupName' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'ReportGroup.reportGroupRoleID' )
         ->options("MBRROLE_FEATURE",'ROLEFEATURE_ID','NAME')
         ->validator( 'Validate::notEmpty' ),
      Field::inst('MBRROLE_FEATURE.NAME')
               ->options("MBRROLE_FEATURE",'ROLEFEATURE_ID','NAME')
         ->validator( 'Validate::notEmpty' ),
      Field::inst( 'ReportGroup.reportGroupDescr' )
      
   )
   ->leftJoin('MBRROLE_FEATURE', "MBRROLE_FEATURE.ROLEFEATURE_ID", '=',"ReportGroup.reportGroupRoleID")
   ->process( $_POST )
   ->json();

javascript

  reportGroup_Editor = new $.fn.dataTable.Editor( {
      ajax: {
         url: "/../../login/mysql/crudReportGroup.php",
      },
      table: "#reportGroupedit",  //HTML table not sql table
      fields: [ {
            label: "GROUP ID:&nbsp;&nbsp;",
            name: "ReportGroup.reportGroupID",
            type: "readonly",
            attr: {
               placeholder: "System generated ID number (Readonly)",
            }
         }, {
            label: "GROUP NAME:&nbsp;&nbsp;",
            name: "ReportGroup.reportGroupName"
         }, {
          label: "ROLE FEATURE NAME:&nbsp;&nbsp;",
          name: "MBRROLE_FEATURE.NAME",
            type: "select"
       }, {
            label: "DESCRIPTION:&nbsp;&nbsp;", 
            name: "ReportGroup.reportGroupDescr",
            type: "textarea"
         }
      ],
      i18n: {
            create: {
                title:  "Create New Role Feature",
            },
            edit: {
                title:  "Edit Role Feature",
            },
            remove: {
                title:  "Delete Role",
                confirm: {
                    1: "Are you sure you wish to delete the checked Feature?",
                }
             }
         },
      display: 'jqueryui',
      formOptions: {
         main: {
            onReturn:   'none'
         }
      }
   });

   // Activate an inline edit on click of a table cell
   $('#reportGroupedit').on( 'click', 'tbody td:not(:first-child)', function (e) {
      console.log('inline edit');
      onReturn: 'none',
      reportGroup_Editor.inline( this, {
         buttons: { 
            label: '<i class="fa fa-check" title="Submit"></i>', 
            fn: function () { 
               this.submit();
               console.log('New Role Fature list');
            }
          }
      });
   });
   
   reportGroup_Editor.on('submitSuccess', function(e, json, data) {
      console.log('report group submit success'); 
      console.log('e: ');
      console.log(e);
      console.log('json: ' );
      console.log(JSON.stringify(json, null, 4));
      console.log('data: ' );
      console.log(JSON.stringify(data, null, 4));
      newRoleFeatureList(document.getElementById('Role2'));
   });
   
   reportGroup_Editor.on('preCreate', function(e, json, data) {
      console.log('report group preCreate'); 
      console.log('e: ');
      console.log(e);
      console.log('json: ' );
      console.log(JSON.stringify(json, null, 4));
      console.log('data: ' );
      console.log(JSON.stringify(data, null, 4));
      newRoleFeatureList(document.getElementById('Role2'));
   });
   
   reportGroup_Editor.on( 'initEdit', function ( node, data, row, type ) {
        console.log( node, data, row, type );
    } );
   
   

   $('#reportGroupedit').DataTable( {
      "paging":         false, 
      "scrollCollapse": true, 
      "scrollY":        "20em",
      "ScrollX":        "100%",
      dom:              "BfTrtip", 
      //dom:              "Tfrtip",
      //dom:              "Tf",
      ajax:             "/../../login/mysql/crudReportGroup.php",
      columns: [
         {  data: null, 
            defaultContent: '', 
            className: 'select-checkbox',
            orderable: false 
         }, 
         { data: "ReportGroup.reportGroupID" },
         { data: "ReportGroup.reportGroupName" },
         { data: "MBRROLE_FEATURE.NAME" },
         { data: "ReportGroup.reportGroupDescr" } 
      ],
      order: [ 1, 'asc' ],
      select: {
            style:    'os',
            selector: 'td:first-child'
      },
      buttons: [
            { extend: "create", editor: reportGroup_Editor },
            { extend: "edit",   editor: reportGroup_Editor },
            { extend: "remove", editor: reportGroup_Editor }
         ]
   });

console.log after inline edit of groupname changing Admin to Adminbbb

inline edit

adminReports.php:842 j…y.Event {type: "initEdit", timeStamp: 1442530190650, jQuery111306483318062964827: true, isTrigger: 2, namespace: ""…} <tr id="row_1" role="row" class="odd">…</tr> Object {DT_RowId: "row_1", ReportGroup: Object, MBRROLE_FEATURE: Object} <td>…</td>
adminReports.php:813 New Role Fature list
adminReports.php:806 inline edit
adminReports.php:820 report group submit success
adminReports.php:821 e: 
adminReports.php:822 j…y.Event {type: "submitSuccess", timeStamp: 1442530228133, jQuery111306483318062964827: true, isTrigger: 2, namespace: ""…}
adminReports.php:823 json: 
adminReports.php:824 {
    "data": [
        {
            "DT_RowId": "row_1",
            "ReportGroup": {
                "reportGroupID": "1",
                "reportGroupName": "Admin",
                "reportGroupDescr": ""
            },
            "MBRROLE_FEATURE": {
                "NAME": "RPTGRP_ADMIN"
            }
        }
    ],
    "error": "",
    "fieldErrors": []
}
adminReports.php:825 data: 
adminReports.php:826 {
    "DT_RowId": "row_1",
    "ReportGroup": {
        "reportGroupID": "1",
        "reportGroupName": "Admin",
        "reportGroupDescr": ""
    },
    "MBRROLE_FEATURE": {
        "NAME": "RPTGRP_ADMIN"
    }
}
adminReports.php:50 0

as you can see the data being sent to the sever script does not have the changes.

Also when adding a new row the windows fail to close and the new row is not added

allan · September 2015

Hi,

Thanks for the details. Could you add a preSubmit event as well, so we can see exactly what is being submitted to the server.

However, what I suspect you are running into is the fact that Editor will only submit the value that has been modified when you are using inline editing, by default. For joined tables you need to have it submit the whole row, so the server-side has the information needed to be able to update the joined table (otherwise it doesn't know what row has been joined).

This can be done using the submit parameter of the form-options object you are passing into the inline() method. There are details about this in the upgrade documentation which is probably worth a read, even if you aren't upgrading but stating a new, as it explains what is happening.

Allan

jonescw · September 2015

here the presubmit data for an inline change (reportGroupName hhhhdddd changed to hhhdddeee)

e
j…y.Event {type: "preSubmit", timeStamp: 1442583669167, jQuery111306972611050587147: true, isTrigger: 2, namespace: ""…}currentTarget: Editordata: undefineddelegateTarget: EditorhandleObj: ObjectisTrigger: 2jQuery111306972611050587147: truenamespace: ""namespace_re: nullresult: undefinedtarget: EditortimeStamp: 1442583669167type: "preSubmit"proto: Object
json:
{
"action": "edit",
"data": {
"row_6": {
"ReportGroup": {
"reportGroupName": "hhhhdddeee"
}
}
}
}

here the presubmit data for an bubble change (reportGroupName hhhhdddd changed to hhhdddeee)

e
j…y.Event {type: "preSubmit", timeStamp: 1442583899301, jQuery111306972611050587147: true, isTrigger: 2, namespace: ""…}
json:
{
"action": "edit",
"data": {
"row_6": {
"ReportGroup": {
"reportGroupID": "6",
"reportGroupName": "hhhhdddeee",
"reportGroupRoleID": "102",
"reportGroupDescr": "bbbbb"
}
}
}
}

here the preSubmit for a new insert: the dialog box does not close and the data is not inserted
e
j…y.Event {type: "preSubmit", timeStamp: 1442583996284, jQuery111306972611050587147: true, isTrigger: 2, namespace: ""…}
json:
{
"action": "create",
"data": {
"0": {
"ReportGroup": {
"reportGroupID": "",
"reportGroupName": "dddddd",
"reportGroupRoleID": "5",
"reportGroupDescr": "ddddddd"
}
}
}
}
data:
"create"

I will go reread the upgrade docs and see what I missed and look over my form-option

fyi: the reportGroupID is an auto increment column.

thanks for looking at this

Is their a way to have it only update or insert into the master table and not the joined tables. I want to display the name (from the joined table) but have the value inserted into the master table.

Currently I have it displaying for insert/change the name in the select box when editing but I can not get the name to display as default. This is done by not using a join but adding the option parameter to the field
Field::inst( 'ReportGroup.reportGroupRoleID' )
->options("MBRROLE_FEATURE",'ROLEFEATURE_ID','NAME')
->validator( 'Validate::notEmpty' ),

jonescw · September 2015

I changed the code to display the joined table column and it still act the same way here the preSubmit for a buble edit

e:
j…y.Event {type: "preSubmit", timeStamp: 1442585140766, jQuery11130466040767962113: true, isTrigger: 2, namespace: ""…}
json:
{
"action": "edit",
"data": {
"row_6": {
"ReportGroup": {
"reportGroupID": "6",
"reportGroupName": "hhhhdddeee",
"reportGroupRoleID": "102",
"reportGroupDescr": "bbbbb"
},
"MBRROLE_FEATURE": {
"NAME": "RPTGRP_PCSUSER"
}
}
}
}
data:
"edit"

jonescw · September 2015

Added the form option you recommended and nothing changed

inline code:
$('#reportedit').on( 'click', 'tbody td:not(:first-child)', function (e) {
console.log('Report inline edit');
report_Editor.inline( this, {
onReturn: 'none',
submit: 'allIfChanged',
buttons: {
label: '<i class="fa fa-check" title="Submit"></i>',
fn: function () {
this.submit();
console.log('Report: newReportList');
}
},
});
});

presubmit output:
e:
j…y.Event {type: "preSubmit", timeStamp: 1442585866823, jQuery111309216401523444802: true, isTrigger: 2, namespace: ""…}
json:
{
"action": "edit",
"data": {
"row_6": {
"ReportGroup": {
"reportGroupName": "hhhhdddeee"
}
}
}
}
data:
"edit"

jonescw · September 2015

Please disregard the last comment changed the wrong piece of code. here the correct information

inline code:
$('#reportGroupedit').on( 'click', 'tbody td:not(:first-child)', function (e) {
console.log('inline edit');
reportGroup_Editor.inline( this, {
onReturn: 'none',
submit: 'allIfChanged',
buttons: {
label: '<i class="fa fa-check" title="Submit"></i>',
fn: function () {
this.submit();
console.log('New Role Fature list');
}
}
});
});

presubmit output
e:
j…y.Event {type: "preSubmit", timeStamp: 1442586474586, jQuery111308602575790137053: true, isTrigger: 2, namespace: ""…}
json:
{
"action": "edit",
"data": {
"row_6": {
"ReportGroup": {
"reportGroupID": "6",
"reportGroupName": "hhhhdddeee",
"reportGroupRoleID": "102",
"reportGroupDescr": "bbbbb"
},
"MBRROLE_FEATURE": {
"NAME": "RPTGRP_PCSUSER"
}
}
}
}
data:
"edit"

jonescw · September 2015

rewrote the server code to use the new Mjoin method but can not get it to work.

code:

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


   Editor::inst( $db) 
   ->table("$dbname.ReportGroup")
   ->pkey('reportGroupID')
   ->fields(
      Field::inst( 'ReportGroup.reportGroupID' ),
      Field::inst( 'ReportGroup.reportGroupName' )->validator( 'Validate::notEmpty' ),
      Field::inst( 'ReportGroup.reportGroupDescr' )
     )
   ->join(
      Mjoin::inst("$dbname.MBRROLE_FEATURE")
         ->link('MBRROLE_FEATURE.ROLEFEATURE_ID', 'ReportGroup.reportGroupRoleID' )
         ->fields(
            Field::inst('NAME' )
         ->set(false)
       )
     )

   ->process( $_POST )
   ->json();

output:

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.ReportGroup JOIN benjiw_researchWeb.MBRROLE_FEATURE ON `benjiw_researchWeb' at line 1","data":[]}

Is there any way to print out the SQL statement you generate?

test SQL statement that works
select ReportGroup.reportGroupID, NAME
from benjiw_researchWeb.ReportGroup
join benjiw_researchWeb.MBRROLE_FEATURE on ReportGroup.reportGroupRoleID = MBRROLE_FEATURE.ROLEFEATURE_ID
;

allan · September 2015

Is there any way to print out the SQL statement you generate?

In the Database/Drivers/Mysql/Query.php file (assuming you are using MySQL) you'll find a line which is commented out which calls file_put_contents. Enable that line and change the output file to one suitable for your system. That will record the SQL that Editor uses.

I'm wondering if it is linked to the use of the database name as well as the table name.

Regards,
Allan

jonescw · September 2015

It does look like it my be related to the use of database name in the table name.

It looks like you do 2 SQL call with my configuration

SQL 1 - works fine
SELECT reportGroupID as 'reportGroupID',
ReportGroup.reportGroupID as 'ReportGroup.reportGroupID',
ReportGroup.reportGroupName as 'ReportGroup.reportGroupName',
ReportGroup.reportGroupDescr as 'ReportGroup.reportGroupDescr'
FROM benjiw_researchWeb.ReportGroup
;

SQL 2 has 3 issues. The issues are # out

SELECT

benjiw_researchWeb.ReportGroup.ROLEFEATURE_ID as dteditor_pkey,

benjiw_researchWeb.MBRROLE_FEATURE.ROLEFEATURE_ID as ROLEFEATURE_ID,
benjiw_researchWeb.MBRROLE_FEATURE.NAME as NAME

FROM benjiw_researchWeb.ReportGroup as `benjiw_researchWeb`.ReportGroup

FROM benjiw_researchWeb.ReportGroup as ReportGroup
JOIN benjiw_researchWeb.MBRROLE_FEATURE

ON `benjiw_researchWeb`.`MBRROLE_FEATURE`.`reportGroupRoleID` = `benjiw_researchWeb`.`ReportGroup`.`ROLEFEATURE_ID`

ON benjiw_researchWeb.MBRROLE_FEATURE.ROLEFEATURE_ID = benjiw_researchWeb.ReportGroup.reportGroupRoleID

issue 1: the dteditor_pkey is matching up the wrong table and column
Issue 2: Database name in the AS clause in the FROM clause
Issue 3 the ON clause of the JOIN clause has the tables and column matched up wrong

My configuration

// DataTables PHP library
require_once dirname(__FILE__).'/../../lib/Editor-PHP-1.5.1/php/DataTables.php'; 

require_once dirname(__FILE__).'/../mysql/dbconnect.php';  // Connect to Database
require_once dirname(__FILE__).'/../lib/userMSG.php';      // Message libray
require_once dirname(__FILE__).'/../../lib/errLib.php';    // Error reporting

// Check make sure the member is logged in
list($sessionData, $sessionRole) = sessionStatus3();

// Alias Editor classes so they are easy to use


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


   Editor::inst( $db) 
   ->table("$dbname.ReportGroup")
   ->pkey('reportGroupID')
   ->fields(
      Field::inst( 'ReportGroup.reportGroupID' ),
      Field::inst( 'ReportGroup.reportGroupName' )->validator( 'Validate::notEmpty' ),
      Field::inst( 'ReportGroup.reportGroupDescr' )
     )
   ->join(
      Mjoin::inst("$dbname.MBRROLE_FEATURE")
         ->link('MBRROLE_FEATURE.ROLEFEATURE_ID', 'ReportGroup.reportGroupRoleID' )
         ->fields(
            Field::inst('ROLEFEATURE_ID'),
            Field::inst('NAME' )
//         ->set(false)
       )
     )

   ->process( $_POST )
   ->json();

jonescw · September 2015

I removed the database name from the configuration and it still creating the dteditor_pkey and join incorrectly.

SELECT  
ReportGroup.ROLEFEATURE_ID as dteditor_pkey, 
MBRROLE_FEATURE.ROLEFEATURE_ID as ROLEFEATURE_ID, MBRROLE_FEATURE.NAME as NAME 
FROM  ReportGroup as ReportGroup  
JOIN MBRROLE_FEATURE 
  ON MBRROLE_FEATURE.reportGroupRoleID = ReportGroup.ROLEFEATURE_ID

jLinux · September 2015

@jonescw I know this is small.. but you know that instead of doing:

console.log('e: ');
console.log(e);

You can just do

console.log('e:', e);

And yes, it still lets you interact with the objects/arrays as if it were in its own line, meaning you can expand them and see the data.

Just sayin :-D

jLinux · September 2015

And for the love of god... use formatting! lol, its impossible to read your code, or the blocks you paste.

allan · September 2015

Hi,

The SELECT query you have shown is the query Editor uses for its MJoin action. There should also be a query immediately preceding that one, another SELECTwhich is performed on theReportGroup` table.

The actual array join is the performed in PHP where the data sets are merged to form the data that should be output to the client-side.

That is shown in the table (in the web-browser) correct? That is the first thing to check there.

The next is, what is the data that is being submitted to the server when editing a row? The 'Network' tab in your browser's developer tools will be able to show you this.

Regards,
Allan

jonescw · September 2015

Allen
I'm going to close this Post and come back to it after I get a simple leftjoin to work.

thanks for you help

joined tables losses data changes

joined tables losses data changes

Answers

benjiw_researchWeb.ReportGroup.ROLEFEATURE_ID as dteditor_pkey,

FROM benjiw_researchWeb.ReportGroup as `benjiw_researchWeb`.ReportGroup

ON `benjiw_researchWeb`.`MBRROLE_FEATURE`.`reportGroupRoleID` = `benjiw_researchWeb`.`ReportGroup`.`ROLEFEATURE_ID`

Howdy, Stranger!

Categories

DataTables

joined tables losses data changes

joined tables losses data changes

Answers

benjiw_researchWeb.ReportGroup.ROLEFEATURE_ID as dteditor_pkey,

FROM benjiw_researchWeb.ReportGroup as benjiw_researchWeb.ReportGroup

ON benjiw_researchWeb.MBRROLE_FEATURE.reportGroupRoleID = benjiw_researchWeb.ReportGroup.ROLEFEATURE_ID

Howdy, Stranger!

Quick Links

Categories

DataTables

FROM benjiw_researchWeb.ReportGroup as `benjiw_researchWeb`.ReportGroup

ON `benjiw_researchWeb`.`MBRROLE_FEATURE`.`reportGroupRoleID` = `benjiw_researchWeb`.`ReportGroup`.`ROLEFEATURE_ID`