Using the EDITOR, Is Inline Editing on multiple tables possible?

webpointz · July 2016

I have the following query that I need to use and I want the user to be able to inline edit the SERIAL NUMBER only.

Is this possible?

Here is the query which returns 4 fields:

SELECT
a.kit_inventory_id AS inventoryid,
c.kit_inventory_items_serial_number AS serialnumber,
c.kit_inventory_items_id AS itemid,
d.kit_items_name AS name
FROM kit_inventory a,
kit_inventory_item_history b,
kit_inventory_items c,
kit_items d,
kit_inventory_items_status e
WHERE a.kit_inventory_id = b.kit_inventory_id
AND b.kit_inventory_items_id = c.kit_inventory_items_id
AND c.kit_items_id = d.kit_items_id
AND c.kit_inventory_items_status_id = e.kit_inventory_items_status_id
AND a.kit_inventory_id = ???;

allan · July 2016

Yes indeed. You would just set it up link a normal Editor, but you must add the primary key value for each of the joined tables that you want to edit. That should be in a hidden field type and its just so that the PHP / .NET libraries can identify the joined row to edit uniquely. The primary key is done automatically for the main host table, but not for the joined tables.

Allan

webpointz · July 2016

Thanks Allan,

Two more things...

Would it be possible for you to show me an example of how all of these joins would work?
Would it work if this query was in a MySQL "View"?

Cheers

allan · July 2016

Would it be possible for you to show me an example of how all of these joins would work?

I'm afraid I don't actually have an example of this available online - although it is something I'm been meaning to blog about for a while! Basically you just need to add the extra Field::inst() for the joined primary key and likewise add another (hidden) field in the Editor Javascript. Obviously you won't include it in the DataTable columns.

Would it work if this query was in a MySQL "View"?

I'm not sure if you can write to a MySQL view can you? It isn't something I've experimented with I'm afraid.

Allan

webpointz · July 2016

Thanks Allan, you're right about the views.

My php code is as follows

Editor::inst( $db, 'kit_inventory', 'kit_inventory_id' )
    ->fields(
            Field::inst( 'kit_inventory_item_history.kit_inventory_items_id' ),
        Field::inst( 'kit_items.kit_items_name' ),
        Field::inst( 'kit_inventory_items.kit_inventory_items_serial_number' )
    )
    ->leftJoin( 'kit_inventory_item_history', 'kit_inventory_item_history.kit_inventory_id', '=', 'kit_inventory.kit_inventory_id' )            
    ->leftJoin( 'kit_inventory_items', 'kit_inventory_items.kit_inventory_items_id', '=', 'kit_inventory_item_history.kit_inventory_items_id' )
    ->leftJoin( 'kit_items', 'kit_items.kit_items_id', '=', 'kit_inventory_items.kit_items_id' )
    ->leftJoin( 'kit_inventory_items_status', 'kit_inventory_items_status.kit_inventory_items_status_id', '=', 'kit_inventory_items.kit_inventory_items_status_id' )
    ->where( 'kit_inventory.kit_inventory_id', 7 )
    ->process($_POST)
    ->json();

My JS code is as follows

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/table.seh_kit_contents.php",
        table: "#example",
        fields: [ {
                label: "ID:",
                name: "kit_inventory_item_history.kit_inventory_items_id"
            },{
                label: "Name:",
                name: "kit_items.kit_items_name"
             },{
                label: "Serial Number:",
                name: "kit_inventory_items.kit_inventory_items_serial_number"
            }
        ]
    } );`

    `// Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            buttons: { label: '&gt;', fn: function () { this.submit(); } }
        } );
    } );

    $('#example').DataTable( {
        dom: "Tfrtip",
        ajax: "../php/table.seh_kit_contents.php",
        columns: [
            { data: "kit_inventory_item_history.kit_inventory_items_id" },
            { data: "kit_items.kit_items_name" },
            { data: "kit_inventory_items.kit_inventory_items_serial_number" }
        ],
        order: [ 0, 'asc' ],
                bPaginate : false,
                bFilter : false,
                
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        }
    } );
} );

I'm not sure where and how I am supposed to put in those hidden fields because as it is, I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '7-27' for key 'PRIMARY'

Also, I need the ??? to be a dynamic number passed through the URL line:

->where( 'kit_inventory.kit_inventory_id', ??? )

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · July 2016

Field::inst( 'kit_items.id' ), // or whatever the pkey column name is

and

{
  name: 'kit_items.id',
  type: 'hidden'
}

Assuming that it is the kit_items.kit_items_name field you want to edit. If you have other fields in other join tables that need to be edited (it looks like you do), you would also need the pkey for that table.

Allan

allan · July 2016

One other thing, if you want to use inline editing, you'll need to add submit: 'allIfChanged' to the object you pass into the inline() method as the second parameter (see form-options for details).

Allan

webpointz · July 2016

Thanks Allan, but I tried that and it still isn't updating any records.

Excuse the code below but this comment window isn't properly blocking "code".

Here's my code (I am currently using the "click" event on the "editor.inline" object to limit inline editing ONLY to the "serial number" field, so not sure how or where to put the
submit: 'allIfChanged'.

Updating doesn't work inline or when I edit the row in a modal window.

When I check the JSON response it has the following (first 3 rows):

data: Array
  0: Object
    DT_RowId: "row_3"
    kit_items: Object
      Kit_items_name: "Tablet"
    kit_inventory_items: Object
      kit_inventory_items_id: "2"
      kit_inventory_items_serial_number: "A123456"
  1: Object
    DT_RowId: "row_3"
    kit_items: Object
      Kit_items_name: "Charge Cord"
    kit_inventory_items: Object
      kit_inventory_items_id: "23"
      kit_inventory_items_serial_number: "B22222"  
  2: Object
    DT_RowId: "row_3"
    kit_items: Object
      Kit_items_name: "Pen"
    kit_inventory_items: Object
      kit_inventory_items_id: "43"
      kit_inventory_items_serial_number: "C234567"

Notice that ALL rows have the DT_RowId as "row_3".

JAVASCRIPT

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/table.seh_kit_contents.php?thisinv=<?php echo $inventoryid; ?>",
        table: "#example",
        fields: [ 
                          {
                label: "Name:",
                name: "kit_items.kit_items_name",
                                type:  "readonly"
              },{
                name: "kit_inventory_items.kit_inventory_items_id",
                                type: "hidden"
              },{
                 label: "Serial Number:",
                 name: "kit_inventory_items.kit_inventory_items_serial_number"
              }
        ]
    } );

    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child, :nth-child(2))', function (e) {
        editor.inline( this, {
            buttons: { label: 'update', fn: function () { this.submit(); } }
        } );
    } );

PHP

Editor::inst( $db, 'kit_inventory', 'kit_inventory_id' )
    ->fields(
                
        Field::inst( 'kit_items.kit_items_name' ),
                Field::inst( 'kit_inventory_items.kit_inventory_items_id' ),
        Field::inst( 'kit_inventory_items.kit_inventory_items_serial_number' )
    )

    ->leftJoin( 'kit_inventory_item_history', 'kit_inventory_item_history.kit_inventory_id', '=', 'kit_inventory.kit_inventory_id' )            
    ->leftJoin( 'kit_inventory_items', 'kit_inventory_items.kit_inventory_items_id', '=', 'kit_inventory_item_history.kit_inventory_items_id' )
    ->leftJoin( 'kit_items', 'kit_items.kit_items_id', '=', 'kit_inventory_items.kit_items_id' )
    ->leftJoin( 'kit_inventory_items_status', 'kit_inventory_items_status.kit_inventory_items_status_id', '=', 'kit_inventory_items.kit_inventory_items_status_id' )
    ->where( 'kit_inventory.kit_inventory_id', $_GET['thisinv'] )
    ->process($_POST)
    ->json();

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · July 2016

so not sure how or where to put the submit: 'allIfChanged'.

Ass it into the object that you pass in as the second parameter to the inline() method - e.g.:

        editor.inline( this, {
            buttons: { label: 'update', fn: function () { this.submit(); } },
            submit: 'allIfChanged'
        } );

When I check the JSON response it has the following (first 3 rows):

Interesting. What is being sent?

Can you give me a link to the page so I can check it out?

Allan

webpointz · July 2016

URL removed

webpointz · July 2016

When I perform an inline edit, here the POST Params:

Form data

action: "edit"
data[kit_items][kit_items_name]: "Tablet"
data[kit_inventory_items][kit_inventory_items_id]: "27"
data[kit_inventory_items][kit_inventory_items_serial_number]: "123123123"
id: "row_7"

The JSON response shows:

row: Object
DT_RowId: "row_7"
kit_items: Object
Kit_items_name: "Tablet"
kit_inventory_items: Object
kit_inventory_items_id: "27"
kit_inventory_items_serial_number: null

No matter which row I update, the JSON response has DT_RowId = "row_7".
The first row has the name "Tablet" but if I update any other line, it temporarily changes the name on the edited line to "Tablet" as well.

webpointz · July 2016

In the page, the PHP is passed a variable of $inventoryid (the same variable in the URL) to be used in the '->where' clause.

Whatever is passed in the where clause is being returned by EVERY row.

webpointz · July 2016

Hi Allan,

Finally have it working. It was the way that the query was constructed. I changed the query (PHP code for left joins):

Editor::inst( $db, 'kit_inventory_items', 'kit_inventory_items_id' )
    ->fields(

         Field::inst( 'kit_items.kit_items_name' ),
         Field::inst( 'kit_inventory_items.kit_inventory_items_serial_number' )
    )
    
    ->leftJoin( 'kit_items', 'kit_items.kit_items_id', '=', 'kit_inventory_items.kit_items_id' )
    ->leftJoin( 'kit_inventory_items_status', 'kit_inventory_items_status.kit_inventory_items_status_id', '=', 'kit_inventory_items.kit_inventory_items_status_id' )
    ->leftJoin( 'kit_inventory_item_history', 'kit_inventory_item_history.kit_inventory_items_id', '=', 'kit_inventory_items.kit_inventory_items_id' )
    ->where( 'kit_inventory_item_history.kit_inventory_id', $_GET["thisinv"] )    
    ->process($_POST)
    ->json();

allan · July 2016

Hi,

Thanks for the updates - great to hear you have it working now.

Allan

Using the EDITOR, Is Inline Editing on multiple tables possible?

Using the EDITOR, Is Inline Editing on multiple tables possible?

Answers

Howdy, Stranger!

Categories

DataTables

Using the EDITOR, Is Inline Editing on multiple tables possible?

Using the EDITOR, Is Inline Editing on multiple tables possible?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables