Select Value Not Updating

benatherton

Hi All,

I have the following code and it's almost perfect apart from when a user changes the select value, this doesn't reflect in the database. Everything else updates as it should do, can anyone see where I have gone wrong here?

Thanks

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../../includes/ajax/load_contracts.php",
        table: "#contract",
        idSrc:  'contract.contractID',
        fields: [ {
                label: "Contract Number:",
                name: "contract.contractNumber"
            }, {
                label: "Contract Start:",
                name: "contract.contractStart"
            }, {
                label: "Contract Term:",
                name: "contract.contractTerm"
            }, {
                label: "Fleet:",
                name: "fleet[].fleetID",
                type: "select"
            }
        ]
    });

    $('#contract').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "../../includes/ajax/load_contracts.php",
            type: 'POST'
        },
        columns: [
            { data: "contract.contractID" },
            { data: "contract.contractNumber" },
            { data: "contract.contractStart" },
            { data: "contract.contractTerm" },
            { data: "fleet", render: "[, ].name" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

<?php
  // DataTables PHP library
  include($_SERVER['DOCUMENT_ROOT'] . '/includes/datatables/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\Upload,
    DataTables\Editor\Validate;
    
  // Build our Editor instance and process the data coming from _POST
  
  Editor::inst( $db, 'contract', 'contract.contractID' )
  ->fields(
    Field::inst( 'contract.contractID' ),
    Field::inst( 'contract.contractNumber' ),
    Field::inst( 'contract.contractStart' ),
    Field::inst( 'contract.contractTerm' )
  )
  
  ->join(
      Mjoin::inst( 'fleet' )
        ->link( 'contract.contractID', 'contract_customer.contractID' )
        ->link( 'fleet.fleetID', 'contract_customer.customerID' )
        
        ->fields(
          Field::inst( 'fleetID' )
            ->options( 'fleet', 'fleetID', 'name' ),
          Field::inst( 'name' )
        )
  )
  ->process( $_POST )
  ->json();
?>

benatherton

Bump

allan

Hi,

Thanks for the code. I think the issue might just be that you need to add multiple: true into the Editor field object for the select field. For example just add multiple: true immediately after line 18.

At the moment the select list will be shown as a single select item only, but the way your PHP code is structured suggests that multiple items can be selected - is that correct?

Thanks,
Allan

benatherton

Hi Allan,

Many thanks for the reply, adding multiple: true does indeed work but I only want one item to be selected, as only one contract can be assigned to one fleet.

What should I change here?

Thanks.

allan

I see - thanks. You probably don't want to use an Mjoin on the server-side then. Is there a contract.fleetID parameter in the contract table that references the foreign key, or is there a separate table that you need to modify the value of? I see the contract_customer table - is that purely a link table (two columns, both foreign keys)? Was it put in explicitly for Editor, or is that something that is required in your database schema for other things?

With a 1:1 mapping, I would normally suggest having a fleetID in your contract table here, but if there is another hop, that is possible.

Allan

benatherton

Hi Allan,

There isn't a contract.fleetID in the contract table and yes the contract_customer is purely a link table between the contract and fleet tables which is required for other things.

Below is the database schema which I hope helps:

-- ----------------------------
--  Table structure for `contract`
-- ----------------------------
DROP TABLE IF EXISTS `contract`;
CREATE TABLE `contract` (
  `contractID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `insurerID` int(10) unsigned DEFAULT NULL,
  `contractReference` char(12) NOT NULL DEFAULT '' COMMENT 'KR 2.1: ''Contract ID'': XXX_YYYYY; XXX = customer code, YYYYY = unique installation number',
  `contractStart` date DEFAULT NULL,
  `contractEnd` date DEFAULT NULL,
  `contractTerm` char(8) NOT NULL DEFAULT '',
  `paymentAmount` decimal(8,2) NOT NULL DEFAULT '0.00',
  `paymentHWAmount` decimal(8,2) NOT NULL DEFAULT '0.00',
  `paymentMonEq` decimal(8,2) NOT NULL DEFAULT '0.00',
  `paymentTypeID` int(10) unsigned DEFAULT NULL,
  `added` datetime NOT NULL,
  `addedBy` char(16) NOT NULL DEFAULT '',
  `lastUpdate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updatedBy` char(16) NOT NULL DEFAULT '',
  `isDeleted` bit(1) NOT NULL DEFAULT b'0' COMMENT 'soft-delete flag',
  PRIMARY KEY (`contractID`),
  KEY `contractReference` (`contractReference`),
  KEY `added` (`added`),
  KEY `isDeleted` (`isDeleted`),
  KEY `fk_contract__paymentTypeID` (`paymentTypeID`),
  CONSTRAINT `fk_contract__paymentTypeID` FOREIGN KEY (`paymentTypeID`) REFERENCES `payment_type` (`paymentTypeID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `contract_customer`
-- ----------------------------
DROP TABLE IF EXISTS `contract_customer`;
CREATE TABLE `contract_customer` (
  `contractCustomerID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contractID` int(10) unsigned NOT NULL,
  `fleetID` int(10) unsigned NOT NULL,
  `added` datetime NOT NULL,
  `addedBy` char(16) NOT NULL DEFAULT '',
  `lastUpdate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updatedBy` char(16) NOT NULL DEFAULT '',
  `isDeleted` bit(1) NOT NULL DEFAULT b'0' COMMENT 'soft-delete flag',
  PRIMARY KEY (`contractCustomerID`),
  KEY `isDeleted` (`isDeleted`),
  KEY `fk_contract_customer__contractID` (`contractID`),
  KEY `fk_contract_customer__fleetID` (`fleetID`),
  CONSTRAINT `fk_contract_customer__contractID` FOREIGN KEY (`contractID`) REFERENCES `contract` (`contractID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_contract_customer__fleetID` FOREIGN KEY (`fleetID`) REFERENCES `fleet` (`fleetID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `fleet`
-- ----------------------------
DROP TABLE IF EXISTS `fleet`;
CREATE TABLE `fleet` (
  `fleetID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(10) unsigned DEFAULT NULL,
  `distributorID` int(10) unsigned DEFAULT NULL,
  `name` char(45) NOT NULL DEFAULT '',
  `added` datetime NOT NULL,
  `addedBy` char(16) NOT NULL DEFAULT '',
  `lastUpdate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updatedBy` char(16) NOT NULL DEFAULT '',
  `isDeleted` bit(1) NOT NULL DEFAULT b'0' COMMENT 'soft-delete flag',
  PRIMARY KEY (`fleetID`),
  KEY `isDeleted` (`isDeleted`),
  KEY `fk_fleet__userID` (`userID`),
  KEY `fk_fleet__distributorID` (`distributorID`),
  CONSTRAINT `fk_fleet__distributorID` FOREIGN KEY (`distributorID`) REFERENCES `distributor` (`distributorID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_fleet__userID` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

Thanks.

allan

Thanks for the schema. it shows that contract_customer isn't actually just a link table, but has a lot of meta information as well. Mjoin isn't suitable for that as it deletes and adds rows rather than updating (this is for Mjoin specifically, a normal table does of course do an update).

So what I think you need are two left joins - one to the link table and one to the fleet table. Have a read over this thread which discusses basically the same thing - updating the joined table.

Allan

benatherton

Hi Allan,

Thanks for the explanation, I have used two joins as you have suggested but now I get the following error when using multiple:true in the select and when I don't use this, the field doesn't update as it should do.

<br />
<b>Notice</b>:  Array to string conversion in <b>/Applications/XAMPP/xamppfiles/htdocs/includes/datatables/Database/Driver/Mysql/Query.php</b> on line <b>93</b><br />
<br />
<b>Notice</b>:  Array to string conversion in <b>/Applications/XAMPP/xamppfiles/htdocs/includes/datatables/Database/Driver/Mysql/Query.php</b> on line <b>93</b><br />
{"data":[{"DT_RowId":"row_2","contract":{"contractID":"2","contractReference":"AAA_A0001","contractStart":"2011-11-10","contractTerm":"FOC"},"fleet":{"fleetID":"2","name":"My Company"}}]}

Below is the updated code:

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
  editor = new $.fn.dataTable.Editor( {
    ajax: "../../includes/ajax/load_contracts.php",
    table: "#contract",
    idSrc:  "contract.contractID",
    fields: [{
      label: "Contract Number:",
      name: "contract.contractReference"
    }, {
      label: "Contract Start:",
      name: "contract.contractStart",
      type: "datetime"
    }, {
      label: "Contract Term:",
      name: "contract.contractTerm"
    }, {
      label: "Fleet:",
      name: "fleet.fleetID",
      type: "select",
      multiple: true
    }]
  });
    
  $('#contract').DataTable( {
    dom: "Bfrtip",
    ajax: {
      url: "../../includes/ajax/load_contracts.php",
      type: "POST"
    },
    columns: [
      { data: "contract.contractReference" },
      { data: "contract.contractStart" },
      { data: "contract.contractTerm" },
      { data: "fleet.name" }
    ],
    select: true,
    buttons: [
      { extend: "create", editor: editor },
      { extend: "edit", editor: editor },
      { extend: "remove", editor: editor }
    ]
  });

<?php
  // DataTables PHP library
  include($_SERVER['DOCUMENT_ROOT'] . '/includes/datatables/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\Upload,
    DataTables\Editor\Validate;
    
  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'contract', 'contract.contractID' )
  ->fields(
    Field::inst( 'contract.contractID' ),
    Field::inst( 'contract.contractReference' ),
    Field::inst( 'contract.contractStart' ),
    Field::inst( 'contract.contractTerm' ),
    Field::inst( 'contract_customer.contractCustomerID' ),
    Field::inst( 'contract_customer.contractID' ),
    Field::inst( 'contract_customer.fleetID' ),
    Field::inst( 'fleet.fleetID' )
      ->options( 'fleet', 'fleetID', 'name' ),
    Field::inst( 'fleet.name' )
  )
    
  ->leftJoin( 'contract_customer', 'contract.contractID', '=', 'contract_customer.contractID' )
  ->leftJoin( 'fleet', 'fleet.fleetID', '=', 'contract_customer.fleetID' )
  
  ->process( $_POST )
  ->json();
?>

Many thanks

allan

Hi,

So the multiple:true probably shouldn't be used now since it is 1:1 - no need to be able to select multiple items.

What I think you want, in PHP is:

    Field::inst( 'contract_customer.contractCustomerID' ),
    Field::inst( 'contract_customer.contractID' ),
    Field::inst( 'contract_customer.fleetID' )
      ->options( 'fleet', 'fleetID', 'name' ),
    Field::inst( 'fleet.name' )

Note that I moved the options to the contract_customer.fleetID field - that is the column you want to edit the value of, so that's the one the options should be applied to.

In Javascript use:

{
      label: "Fleet:",
      name: "contract_customer.fleetID",
      type: "select"
},
{
      name: "contract_customer.contractCustomerID",
      type: "hidden"
}

So basically you want to edit the value of contract_customer.fleetID (with a value from fleet.fleetID), and you need to submit the primary key for the joined table you want to edit (contract_customer.contractCustomerID in this case).

Does that clarify what is needed?

Regards,
Allan

benatherton

Hi Allan,

That's perfect, I now have it working exactly as I need it to

Many thanks for all your help with this.