Inline edit to a table that changes databases on user select.

ztaylor1990

I have a project where I have one Editor table where the data changes in that table depending on which project the user selects. I am changing the data that is shown to the user in my editor table from a select drop-down list. The user will select which project they want to see and on selection, I am sending the variable to my server-side script and using that value to decide which table to pull data from. If there is not a user selection then the default table will show on the client-side. The problem that I am having is when the table data changes on the client-side from a user selection and I edit the table the change is taking place in the default table data instead of the table that the user selected. When I check this in the Network Response I can see after making the edit the server-side script runs twice, which it is supposed to happen to my understanding. The first script shows the DT_RowID is the data from the table that the user selected but when I make the inline edit the server-side script runs again but this time the DT_RowID has changed back to the default table and updated that table instead of the one the user selected. So is there a way to hold that DT_Row ID until the data has been submitted to the db? I am not familiar enough with the process of how the editor works to know where the issue is occurring. Any advice would help. Thanks

allan

It sounds like the database variable might not be getting sent with the Editor Ajax request perhaps? Can you show me the code you are using please?

Thanks,
Allan

ztaylor1990

That is what it seems like as well. Here is my code for that.

//current working file Main1
function rfpworking() {
  $(document).ready(function() {
    var val = $('#project_name').val();
    var editor = new $.fn.dataTable.Editor( {
      "processing": true,
      "scrollX": true,
      ajax: {
        url: '../dependencies/datatables/php/rfpworking_table_serverside.php',
        "type": "POST",
        data: function ( d ) {
          d.val = val;
        }
      },
      "table": "#rfpworking",
    formOptions: {
  bubble: {
    submit: 'allIfChanged'
  }
},    
      fields: [ {
        label: "Origin City",
        name: "origin_city"
      }, {
        label: "Origin State",
        name: "origin_state"
      }, {
        label: "Destination City",
        name: "destination_city"
      }, {
        label: "Destination State",
        name: "destination_state"
      }, {
        label: "Truck Type",
        name: "truck_type"
      }, {
        label: "Companies",
        name: "spot_num_companies"
      }, {
        label: "Reports",
        name: "spot_num_reports"
      }, {
        label: "Our Avg LH",
        name: "our_avg_lh"
      }, {
        label: "Our Num Reports",
        name: "our_num_reports"
      }, {
        label: "Ann Volume",
        name: "ann_volume"
      }, {
        label: "Cust Miles",
        name: "cust_miles"
      }, {
        label: "Market Avg LH Dat",
        name: "market_avg_lh_dat"
      }, {
        label: "DAT Miles",
        name: "dat_miles"
      },{
        label: "Avg RPM",
        name: "avg_rpm"
      }, {
        label: "Carrier Fuel",
        name: "carrier_fuel"
      }, {
        label: "Cust Fuel",
        name: "cust_fuel"
      }, {
        label: "Margin %",
        name: "percent_margin"
      }, {
        label: "Margin Flat:",
        name: "margin_flat"
      }, {
        label: "Cust Flat",
        name: "cust_flat"
      }, {
        label: "Cust All In",
        name: "cust_all_in"
      }, {
        label: "Carrier Flat",
        name: "carrier_flat"
      }, {
        label: "Carrier All In",
        name: "carrier_all_in"
      }, {
        label: "All In GP",
        name: "all_in_gp"
      },{
        label: "GP %",
        name: "gp_percent"
      },{
        label: "Spot Low",
        name: "spot_low"
      }, {
        label: "Spot High",
        name: "spot_high"
      }, {
        label: "STD",
        name: "std"
      }, {
        label: "Time",
        name: "time"
      }, {
        label: "Hazmat (Yes)",
        name: "hazmat"
      },{
        label: "Lane ID",
        name: "lane_id"
      },{
        label: "extra1",
        name: "extra1"
      },{
        label: "extra2",
        name: "extra2"
      },{
        label: "extra3",
        name: "extra3"
      },{
        label: "extra4",
        name: "extra4"
      }]
    } );
    $('#rfpworking').on( 'click', 'tbody td', function (e) {
editor.inline(this, {
  submit: 'allIfChanged'
});
    } );
    var rfpworking = $('#rfpworking').DataTable({
      dom: "Bfrtip",
      destroy: true,
      ajax: {
        url: '../dependencies/datatables/php/rfpworking_table_serverside.php',
        "type": "POST",
        data: function ( d ) {
          d.val = val;
        }
      },
      serverSide: true,
      columns: [
        { data: "origin_city"},
        { data: "origin_state" },
        { data: "destination_city" },
        { data: "destination_state" },
        { data: "truck_type" },
        { data: "spot_num_companies" },
        { data: "spot_num_reports" },
        { data: "our_avg_lh" },
        { data: "our_num_reports" },
        { data: "ann_volume" },
        { data: "cust_miles" },
        { data: "market_avg_lh_dat" , render: $.fn.dataTable.render.number(',', '.', 2)},
        { data: "dat_miles" },
        { data: "avg_rpm" },
        { data: "carrier_fuel" },
        { data: "cust_fuel" },
        { data: "percent_margin" },
        { data: "margin_flat", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "cust_flat", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "cust_all_in", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "carrier_flat", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "carrier_all_in", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "all_in_gp", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "gp_percent", render: $.fn.dataTable.render.number(',', '.', 2) },
        { data: "spot_low" },
        { data: "spot_high" },
        { data: "std" },
        { data: "time" },
        { data: "hazmat" }, 
        { data: "lane_id" }, 
        { data: "extra1" }, 
        { data: "extra2" }, 
        { data: "extra3" },
        { data: "extra4" }
      ],
        autoFill: {

            editor:  editor
        },
        keys: {
            editor:  editor
        },
      select: {
        style:    'os',
        selector: 'td:first-child'
      },
    });
  });
};
     function sendData(){
      var val = $('#project_name').val();
      rfpworking();
      rfp();
      rfp18();
      awards();
      awards18();
      var x = document.getElementsByClassName("downloadTableID");
      var i;
      for (i = 0; i < x.length; i++) {
        x[i].value = $('#project_name').val();
      }
    }

And this is what I am doing on the server-side.

<?php
include_once $_SERVER['DOCUMENT_ROOT'].'/dependencies/conn.php';
setlocale(LC_MONETARY, 'en_US.UTF-8'); 
if($_POST['val'] == ''){
     $var_value = 'rfp_table';
}else{
$var_value = $_POST['val'].'_rfp_table';    
}
$haz = "SELECT * FROM haz_miles";
$query2= mysqli_query($conn, $haz);
    while($row2 = $query2->fetch_assoc()) {
        $haz1 = $row2['haz1'];
        $haz2 = $row2['haz2'];
        $haz3 = $row2['haz3'];
        $haz4 = $row2['haz4'];
    }
mysqli_free_result($result2);
?>

<?php
include( "../../../Editor-1.9.2/lib/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
Editor::inst( $db, $var_value )
    ->fields(
    Field::inst( 'origin_city' ),
    Field::inst( 'origin_state' ),
    Field::inst( 'destination_city' ),
    Field::inst( 'destination_state' ),
    Field::inst( 'truck_type' ),
    Field::inst( 'spot_num_companies' ),
    Field::inst( 'spot_num_reports' ),
    Field::inst( 'our_avg_lh' ),
    Field::inst( 'our_num_reports' ),
    Field::inst( 'ann_volume' ),
    Field::inst( 'cust_miles' ),
    Field::inst( 'market_avg_lh_dat' ),
    Field::inst( 'dat_miles' ),
    Field::inst( 'avg_rpm' ),
    Field::inst( 'carrier_fuel' ),
    Field::inst( 'cust_fuel' ),
    Field::inst( 'percent_margin' ),
    Field::inst( 'margin_flat' )
    ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'cust_flat' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'cust_all_in' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'carrier_flat' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'carrier_all_in' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'all_in_gp' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'gp_percent' )
        ->setFormatter( function ( $val, $data, $opts ) {
        return round($val, 2);
    } ),
    Field::inst( 'spot_low' ),
    Field::inst( 'spot_high' ),
    Field::inst( 'std' ),
    Field::inst( 'time' ),
    Field::inst( 'hazmat' ),
    Field::inst( 'lane_id' ),
    Field::inst( 'extra1' ),
    Field::inst( 'extra2' ),
    Field::inst( 'extra3' ),
    Field::inst( 'extra4' ),
    Field::inst( 'haz1' )->set( Field::SET_CREATE )
    )

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

?>

allan

Could you put the

var val = $('#project_name').val();

line inside the data function (for both Editor and DataTables) please? At the moment it is only being evaluated once - when the page is loaded. If it can change, it won't have any effect on the submitted data.

Allan

ztaylor1990

Wow, I can't believe I missed that. It is working now. I actually have it like that on another table where I am using that function. Thank you so much for your help.