Can't update record which uses joins to get get select options

michaelosolinski

Hi There,

I have two tables, tblTariffs and tblRentalPeriodProfiles. tblTariffs contains an ID from tblRentalPeriodProfiles. The structure of those tables is thus:

tblRentalPeriodProfiles

profileID
profileName
profileDescription


tblTariffs

tariffID
tariffName
rentalPeriodProfileID
tarifftoCopy

I have created a join between profileID and rentalPeriodProfileID with the intention that when the front end form is viewed to edit tblTariffs records then the appropriate profileName value is displayed in a select box based on the rentalProfileID value. That part works ok however when I try to create a new record for tblTariffs or edit an existing one I get "An error has occurred please contact a system administrator"

Even though this error occurs, Any edits that I make to the other two fields in the form are applied correctly and the database is updated.

I've checked the form data being passed via the Network tool in Chrome and the expected values are being passed. It is just thatthe profileID field isn't being updated for an edit and no value is being entered for a new record.

Please see my code below and apologies for any lack of clarity in my explanation. I am not a full time developer so I may well be making a very obvious mistake.

Kind Regards

Mike

The Javascript

[code]

$(document).ready(function() {

var editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "table.createTariff.php",
"domTable": "#createTariff",
"fields": [
{
"label": "Tariff Name",
"name": "tariffName",

},
{
"label": "Rental Period Profile",
"name": "tblRentalPeriodProfiles.profileID",
"type": "select"

},

{
"label": "Tariff to Copy",
"name": "tarifftoCopy",


}

]
} );

$('#createTariff').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "table.createTariff.php",
"aoColumns": [
{
"mData": "tariffName"
},
{
"mData": "tblRentalPeriodProfiles.profileName",
"sDefaultContent": ""

},
{
"mData": "tarifftoCopy"
}
],
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
},
"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select and radio fields based on
// what is available in the database

editor.field('tblRentalPeriodProfiles.profileID').update( json.tblRentalPeriodProfiles);


}
} );
} );

[/code]




The PHP

[code]
$pkey = 'tariffID';

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'tblTariffs', $pkey )
->field(

Field::inst( 'tariffName' ),
Field::inst( 'rentalPeriodProfileID' ),
Field::inst( 'tarifftoCopy' )
)

->join(
Join::inst( 'tblRentalPeriodProfiles', 'object' )
// the first value is the parent id, the second is the child id
->join( 'rentalPeriodProfileID', 'profileID' )
->set ( 'false' )
->field(
Field::inst( 'profileName' ),
Field::inst( 'profileID' )
->set ( 'false' )
)
);


// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();


// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'department' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
// Get Rental Period Profile Name details
$out['tblRentalPeriodProfiles'] = $db
->select( 'tblRentalPeriodProfiles', 'profileID as value, profileName as label' )
->fetchAll();

}

// Send it back to the client

echo json_encode( $out );

[/code]

allan

Hi Mike,

Thanks for the clear and concise description - brilliant :-)

I think the problem is with this:

> "name": "tblRentalPeriodProfiles.profileID",

I think that should be:

[code]
"name": "rentalPeriodProfileID",
[/code]

i.e. you want to update the reference to the external table do you not? You've got `->set(false)` for the join which is perfect, but at the moment there is no data being submitted for the `rentalPeriodProfileID` field. With that change, hopefully that will do it!

Regards,
Allan

michaelosolinski

Hi Allan,

Many thanks for the speedy response! Yes, when editing a record, in the select box, I want to get the profile name from tblRentalPeriodProfiles that corresponds with the ID in tblTariffs and if creating a new record just want the select to be populated with all of the options.

From what you say above:

[code]
"label": "Rental Period Profile",
"name": "rentalPeriodProfileID",
"type": "select"

[/code]

When I do this, when initially viewing the datatable, the Rental Period Profile column is correctly populated however when I click edit or new then the select box is no longer populated and clicking the update button yields the error.

[code]


Notice: Undefined index: tblRentalPeriodProfiles in /DataTables/extras/Editor/php/lib/Editor/Join.php on line 496

{"id":"row_33","error":"","fieldErrors":[],"data":[],"row":{"DT_RowId":"row_33","tariffName":"second after test","rentalPeriodProfileID":"0","tarifftoCopy":"22","tblRentalPeriodProfiles":{}}}
[/code]

One other thing that I have since realised is that with my original code, when clicking update, I am getting the following response:

[code]
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2' for key 'PRIMARY''
[/code]

Once again thanks for your assistance and I'm sorry if I have misunderstood your advice above in any way.

Kind Regards

Mike

allan

Hi Mike,

Which version of the Editor PHP libraries are you using? Line 496 in the latest version is an echo of an error state, so I'm guessing you might be using an old version? Could you update the libraries to 1.2.4 please? https://editor.datatables.net/download .

Thanks,
Allan