Local table editing & calling a sql stored proc to save

crcucb · July 6

I would like to make the editing process so that when they click edit, a forn pops up that will have multiple check boxes or drop downs, a multi select, and comments. when it's saved, it won't actually use the php to update the source bu call a mssql stored proc passing all the values from the form to save back to the db. there maybe multiple updates based on what exactly the user selected in the form. I'm pretty good with sql but been learning datatools. From what I read, using local table editing is possibly what I need to create a space with all of the fields of information used to present to the user for edits. How do I collect the data, construct a string to call a stored proc?

allan · July 6

Hi,

You don't need to use the provided server-side libraries for Editor, you can have your own, and that is what I would suggest in this sort of case. The data sent to the server for the Ajax request is fully documented and I'm happy to answer any questions you might have about it. It also shows the data that Editor expects back. What the server does with the data sent and how it gets the data to send back is a black box as far as Editor is concerned. It could go through a quantum computer on the surface of a black hole, with multiple AGIs managing it for all the Javascript cares .

The idea of that was for exactly this sort of case when you have your own data handling on the server-side that you want to use. The Editor PHP (.NET and Node.js) libraries aren't designed to cover all use cases - the 80/20 rule applies!

Allan

crcucb · July 7

Thank you, I am on a time crunch and did not want to spend time going down the wrong road. Is there any concern if I let the edit post back to the PHP and inspect the action for Edit, then perform the updates there? I had a previous question about reviewing the $_POST and the depth of the arrays, and you suggested using the editor libraries. Am I able to use the editor libraries to get all of the data elements then call the stored procs?

allan · July 7

Is there any concern if I let the edit post back to the PHP and inspect the action for Edit, then perform the updates there?

No.

I had a previous question about reviewing the $_POST and the depth of the arrays, and you suggested using the editor libraries.

Lol - sorry, I hadn't connected the two threads together in my head.

Am I able to use the editor libraries to get all of the data elements then call the stored procs?

No - not in the PHP ones (there is a model for it in the .NET libraries, but not PHP).

Okay, so joining the dots now, if you want to use a stored proc. you don't have the option of using the provided libraries for Editor at the moment (one day I'll add support for it, but that isn't in yet).

So you'll need to parse the data that is submitted. It isn't particularly complex data - the action is trivial, and the data property is nested with the row ids (to allow multirow editing) and the field values - so:

foreach( $_POST['data'] as $pkey => $fields) {
  exec_my_stored_proc($pkey, $fields['myField1'], $fields['myField2'], ...);
}

You need to know the field names with the above, but presumably you do. You could create an abstraction layer if you want, but if you need to get a crack on, this would be the way to do it.

You need to make sure that the JSON data you return in response to the edit (for the row being edited) is in the same format as the data you used to load the table initially. Normally you've just run it through the same SELECT function but with a WHERE condition applied for the rows in question.

What you might find useful with this sort of thing, is to look at the Ajax data tab in an example like this one so you can see what data is being submitted to the server and what is being returned - so you know what your script needs to handle and what to return for the same actions.

Allan

crcucb · July 9

Thank you, you are always very helpful

crcucb · July 14

Allen, a follow up to "You need to make sure that the JSON data you return in response to the edit (for the row being edited) is in the same format as the data you used to load the table initially. Normally you've just run it through the same SELECT function but with a WHERE condition applied for the rows in question.".

Currently I have a datatable whose source is ajax: 'php/Addresses_Address_DT.php' which is using the server side PHP editor to populate.

The datatable has an Edit button which is utilizing addresseditor which also relies on ajax: 'php/Addresses_Address_DT.php' but has additional fields that are not defined in Addresses_Address_DT.php' . When data is saved in the editor, DT will call php/Addresses_Address_DT.php' again. In that code, is it possible I inspect the Action to see that I am saving, perform the necessary SQL updates then afterwards, let the Addresses_Address_DT.php continue to run to return data back to the client side? If so, is there something I need to do in php/Addresses_Address_DT.php to tell it don't worry about trying to update anything, just return data?

Or, do I need to use a different PHP from addresseditor which is doing it's own querying and updating for the specific record I am editing? If this is the case, is there a query I need to do at the end so the client side datatable doesn't get mucked up?

Below are the contents of Addresses_Address_DT.php, note I am hardcoding an AddressAID for now.

<?php

error_reporting(E_ALL);
ini_set('display_errors', 'On');

/*
 * Editor server script for DB table Addresses
 * 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;

// Build our Editor instance and process the data coming from _POST

Editor::inst( $db, 'Addresses', 'AddressAID' )

    ->fields(

        Field::inst( 'Addresses.streetname' ),
        Field::inst( 'Addresses.addressnumber' ),
        Field::inst( 'Addresses.address2' ),
        Field::inst( 'Addresses.city' ),
        Field::inst( 'Addresses.state' ),
        Field::inst( 'Addresses.ward' ),
        Field::inst( 'Addresses.district' ),
        Field::inst( 'Addresses.addressnotes' ),
        Field::inst( 'Addresses.nors' ),
        Field::inst( 'Addresses.lat' ),
        Field::inst( 'Addresses.long' ),
        Field::inst( 'Addresses.addressinactive' ),
        Field::inst( 'Addresses.AddressAID'),
      
        Field::inst('view_Addresses.Full_Address')
            ->options( Options::inst()
            ->order( 'view_Addresses.Full_Address' )
        )
        ,
      Field::inst('view_Addresses.Names'),
    Field::inst('view_Addresses.Parties'),
    Field::inst('view_Addresses.Hung_Hanger'),
    Field::inst('view_Addresses.Left_Card'),
    Field::inst('view_Addresses.Spoken_With'),
    Field::inst('view_Addresses.Confident'),
    Field::inst('view_Addresses.Jordan_Followup'),
    Field::inst('view_Addresses.Okay_to_Text_or_Email'),
    Field::inst('view_Addresses.Will_Volunteer'),
    Field::inst('view_Addresses.Num_Residents'),
    
     Field::inst('view_Addresses.Cannot_not_find_Address'),
      Field::inst('view_Addresses.Vacant'),
     Field::inst('view_Addresses.No_Tresspassing'),
      Field::inst('view_Addresses.Cannot_access'),
      
    
    Field::inst('view_Addresses.Ignore'),
    Field::inst('view_Addresses.Inactive')
    
   ->where( 'Addresses.AddressAID', 56, '=' )

    ->leftJoin('view_Addresses', 'view_Addresses.AddressAID', '=', 'Addresses.AddressAID')

  ->debug(true) // Add this line
    ->process( $_POST )

    ->json();

allan · July 14

If you could show me the client-side code, that would be useful. I'm struggling to understand the full set up at the moment (that might just be me - its been a long few days!).

It sounds like you might be using server-side processing for the DataTable? If that's the case, just have the response to the Editor Ajax call be {"data":[]} and then the redraw of the DataTable will fetch the new data from the server.

Allan

crcucb · July 14

That's fine, It's client side, the js is below.

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

$(document).ready(function() {
    

    
var usersEditor = new DataTable.Editor({
    ajax: {
        url: 'php/Addresses_Residents_Nested_DT.php'
    },
    fields: [
        {
            label: 'FullName:',
            name: 'view_Residents.FullName'
        }
    ]
});

//addEventListener("DOMContentLoaded", function () {
let maintable;
var addresseditor = new DataTable.Editor( {
        ajax: 'php/Addresses_Address_DT.php',
        table: '#Addresses',
        idSrc:  "Addresses.AddressAID",
        searching: false,
        fields: [

            {
                "label": "Address Notes:",
                "name": "Addresses.addressnotes",
                "type": "textarea"
            },
       {
            label: 'Res Pick',
                name: 'FullNameParty',
            type: 'tags',
            limit: 6,
             options: [
            { label: 'Beijing', value: 1 },
            { label: 'London', value: 2 },
            { label: 'New York', value: 3 },
            { label: 'Paris', value: 4 },
            { label: 'Rome', value: 5 },
            { label: 'Sydney', value: 6 }
        ]
          
        },  
         {
                label: "Hung Hanger",
                name: "view_Addresses.Hung_Hanger",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,
                  {
                label: "Left Card",
                name: "view_Addresses.Left_Card",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,
                 

              {
                label: "Cannot find Address",
                name: "view_Addresses.Cannot_not_find_Address",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,
                          {
                label: "Vacant",
                name: "view_Addresses.Vacant",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,
                 {
                label: "No Tresspassing",
                name: "view_Addresses.No_Tresspassing",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,
                 {
                label: "Cannot Access",
                name: "view_Addresses.Cannot_access",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }  ,       
        
            
               
              {
                label: "AddressInactive:",
                name: "Addresses.addressinactive",
                type: "checkbox",
                separator: "",
                options: [
                    { label: "", value: 1 }
                                    ]   ,
                unselectedValue : 0
            }
            
                        
            ,{
                label: 'Residents:',
                name: 'Addresses.AddressAID',
                type: 'datatable',
                dom: 'lrtip', 
                submit: false,
                      searching: false,
                      paging: false,
                        info: false,
                layout: {
             topStart: null
            }   ,               
             select:false
                        
            }
            
                                
            }
        
        
            
            
        ]
        
        
         
    } ); //     var editor = new DataTable.Editor( {

 maintable = new DataTable('#Addresses', {
        
        ajax: 'php/Addresses_Address_DT.php',
        idSrc:  "Addresses.AddressAID",
        columns: [
                { title: 'Full Address', "data": "view_Addresses.Full_Address", searchable: true }  ,
        { title: 'Hung Hanger',  "data": "view_Addresses.Hung_Hanger", searchable: false, className: 'dt-column-header' },  
        { title: 'Spoken With',  "data": "view_Addresses.Spoken_With", searchable: false },  
        { title: 'Confident',  "data": "view_Addresses.Confident", searchable: false },  
        { title: 'Jord FU',  "data": "view_Addresses.Jordan_Followup", searchable: false },  
        { title: 'Txt or Email',  "data": "view_Addresses.Okay_to_Text_or_Email", searchable: false },  
        { title: 'Vol',  "data": "view_Addresses.Will_Volunteer", searchable: false   },  
        { title: '# Res',  "data": "view_Addresses.Num_Residents", searchable: false },  
                { title: 'Parties', "data": "view_Addresses.Parties", searchable: true },
                { title: 'Address Notes', "data": "Addresses.addressnotes", className: 'limit-wrap-text' ,render: DataTable.render.ellipsis( 25 ), searchable: true },          
                {   title: 'Ward', "data": "Addresses.ward", searchable: false},
                {   title: 'Dist',  "data": "Addresses.district", searchable: false  },
                {   title: 'N or S',  "data": "Addresses.nors", searchable: false   },
                { title: 'Lat', "data": "Addresses.lat", searchable: false },
                { title: 'Long', "data": "Addresses.long", searchable: false    },
                { title: 'Residents',  "data": "view_Residents", render: '[, ].FullName', searchable: false },
                { title: 'Ignore',  "data": "view_Addresses.Ignore", searchable: false },  
                { title: 'Inactive', "data": "view_Addresses.Inactive", searchable: false }
                
        ],
        serverSide: false,
        //pageLength: 10,
        lengthMenu : [10, 50],
        paging: true,
        info: false,
        autoWidth: false,
        searching: true,
        scrollY: '50vh',
        autoWidth: true,
        
        columnDefs: [{
            targets: [1,2,3,4,5,6,16,17],
            render: function (data, type, row) {
        
                    if (type == 'display' && data == 0) {
                        return '';
                        }
                            // return data;
                            return $.fn.dataTable.render.number(null, '.', 1,null).display(data);
                        }
        }],
        
        columnDefs: [ { className: "my_col_class", targets: [1,2,3,4,5,6,16,17]  }],
        
    responsive: false,
        order: [[0, 'asc']],
        rowId: 'Addresses.AddressAID',
    stateSave: true,
    scrollCollapse: true,
    scroller: true,
    scrollX: true,
    fixedColumns: true,
    select: {
        style: 'single',
        selector: 'td:not(:first-child)'
    },

      layout: {
            topStart: {
                buttons: [
                      ['colvis', 'createState', 'savedStates'],
                    { extend: 'edit', editor: addresseditor, text : 'Edit Address' },
                   // 'searchPanes'
                   'searchBuilder', 'pageLength'
                ]
            }
        },
            
       // colReorder: true,
        colReorder: {
            columns: ':not(:first-child)'
         },
        
        
        rowCallback: function (row, data) {
            //console.log(" rowcallback");
            
            var maintable = $('#Addresses').DataTable();
           
            

    }
        


    
        
    });  // maintable = new DataTable('#Addresses', {
    
    


            addresseditor.on('open', function () {
                    addresseditor
                        .field('Addresses.AddressAID')
                        .dt()
                        .ajax.reload(function (json) {
                            console.log("opened editor");
                          //  usersEditor.field('Addresses.AddressAID').update(json.options['Addresses.AddressAID']);
                        });
                                  
                                  

                
                                    $.ajax({
                                 url: '/php/Addresses_Residens_Children_Ajax.php', // Replace with your server-side endpoint
                                    method: 'GET',
                                    data : {AddressIAD: 46},
                                    
                                    
                              success: function (data) {
                                        console.log("returned");

                                            
                                                 },
                                                        error: function (xhr, error, thrown) {
                                                      console.error("Error fetching tags:", error);
                                             }
                        });
      
          });


}); // $(document).ready(function(


            
$('#size').on( 'change', function () {
  var table = $('#Addresses').DataTable();
     
     
    var data = table.rows().data();
    var newSize = this.value+ 'em';
    var myElement = document.getElementById('Addresses');
      myElement.style.fontSize = newSize;
     table.columns.adjust().draw();
     

     var elements =  document.getElementsByClassName('dt-container dts DTS dt-empty-footer');


            
    for (var i = 0; i < elements.length; i++) {
        elements[i].style.fontSize = newSize;
    };


 
    console.log("just adjusted ", newSize);
  } );  // $('#size').on( 'change', function ()
  

;

Edited by Allan to add syntax highlight

allan · July 15

Thank you. So it doesn't have server-side processing enabled. Currently when your Editor form submits it goes to php/Addresses_Address_DT.php and it expects back the data for the edited row, per the docs here.

I'm not understanding where your stored procedure is coming into this? I don't see it in any of the above code. Is that the problem - you aren't sure where to put it?

If that's the case, you need to have a new PHP file (or an if condition in the current one for when data is submitted) which will accept the data submitted by Editor, process it and then return the JSON for the edited rows.

Allan

Local table editing & calling a sql stored proc to save

Local table editing & calling a sql stored proc to save

Answers

Howdy, Stranger!

Categories

DataTables

Local table editing & calling a sql stored proc to save

Local table editing & calling a sql stored proc to save

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables