Inline Edit creates new phantom "a a a a " row in database.
Inline Edit creates new phantom "a a a a " row in database.
I've successfully implemented Datatables / Editor, and an initial inline edit works great. Successive edits produce the error "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'a-0000-a-a' for key 'name_modelyear_brand'. This is because after the proper edit, a new "phantom" row is inserted into my database with an 'a' for every row that allows alpha characters that will violate my unique key rules. If I remove the extra row, I can again edit another cell - but the phantom 'a a a' row is added again.
I'm using the latest versions of Datatables, Editor (1.5.5) and the PHP libraries. My database has a primary key called 'unit_id' which is properly defined here:
PHP portion: (I've removed all validation rules for the sake of troubleshooting)
$editor = Editor::inst( $db, 'mequipmodels', 'unit_id' );
$editor->fields(
Field::inst( 'modelyear' ),
Field::inst( 'brand' ),
Field::inst( 'name' ),
Field::inst( 'model' ),
Field::inst( 'type' ),
Field::inst( 'baseimage' ),
Field::inst( 'stockpipe' ),
Field::inst( 'lengthf' ),
Field::inst( 'lengthi' ),
Field::inst( 'dryweight' ),
Field::inst( 'dryhitchweight' ),
Field::inst( 'wcapacity' ),
Field::inst( 'gvwr' ),
Field::inst( 'gcwr' ),
Field::inst( 'bunks' ),
Field::inst( 'sleepcap' ),
Field::inst( 'slideouts' ),
Field::inst( 'awnings' ),
Field::inst( 'f_water' ),
Field::inst( 'g_water' ),
Field::inst( 'b_water' ),
Field::inst( 'usecount' ),
Field::inst( 'verified' ),
Field::inst( 'longname' ),
Field::inst( 'keywords' ),
Field::inst( 'company' ),
Field::inst( 'modifier' )
);
$editor->process( $_POST );
$editor->json();
Javascript:
// datatables editor
editor = new $.fn.dataTable.Editor( {
ajax: "../fx_rev/traverse_rev.php",
table: "#inventory",
fields: [ {
label: "Length (F):",
name: "lengthf"
},
{ label: "Length (I):",
name: "lengthi"
},
{ label: "dryweight",
name: "dryweight"
},
{ label: "dryhitchweight",
name: "dryhitchweight"
},
{ label: "wcapacity",
name: "wcapacity"
},
{ label: "gvwr",
name: "gvwr"
},
]
} );
// datatables
var oTable = $('#inventory').DataTable({
ajax: "../fx_rev/traverse_rev.php?stype=inuse",
dom: 'flTBrtip',
processing: true,
pageLength: 25,
responsive: true,
colReorder: {fixedColumnsLeft: 6},
columns: [
{ data: 'usecount', className: "ignore highlight" },
{ data: 'verified' },
{ data: "modelyear", className: "ignore gettab1" },
{ data: "brand", className: "ignore gettab1" },
{ data: "model", "width": "10%", className: "ignore gettab1" },
{ data: "name", className: "ignore gettab1" },
{ data: "type", className: "ignore gettab1" },
{ data: "baseimage", className: "ignore gettab1" },
{ data: "stockpipe", className: "ignore gettab1" },
{ data: "longname", className: "ignore gettab3" },
{ data: "lengthf", className: "editable active" },
{ data: "lengthi", className: "editable active" },
{ data: "dryweight", className: "editable active" },
{ data: 'dryhitchweight', className: "editable active" },
{ data: "wcapacity", className: "editable active" },
{ data: 'gvwr', className: "editable active" },
{ data: "company" },
{ data: "modifier" },
{ data: "keywords" },
],
buttons: [
'copy', 'excel', 'pdf'
],
select: {
style: 'os',
items: 'row',
selector: 'td:not(.editable):not(.ignore)',
className: 'info'
},
keys: {
columns: '.editable',
keys: [ 9,38,40 ]
},
rowCallback: function(row,data) {
// left clicks
$(".gettab3",row).click(function() {
manageEntry(data['modelyear'],data['brand'],data['model'],data['name'],'#tab3');
$(this).parent().addClass('success');
})
$(".gettab1",row).click(function() {
manageEntry(data['modelyear'],data['brand'],data['model'],data['name'],'');
$(this).parent().addClass('success');
})
$(".highlight",row).click(function() {
$(this).parent().toggleClass('warning');
})
}
})
oTable.on( 'key-focus', function ( e, datatable, cell ) {
editor.inline( cell.index(), {
onBlur: 'submit'
} );
} );
Other than the phantom entry, everything appears to be working as it should. The "create" action is not being fired by the browser. The Firefox developer console shows that a submitted inline edit sends correct form data, for example editing the gvwr cell on unit_id 7943 submits:
action: edit
data[row_7943][gvwr]: 8627
Could the problem be my database? Aside from having a unit_id primary key, which was added specifically for Datatables to identify rows, it also has a 'name_modelyear_brand' unique key that contains four cells. Could this be confusing the Editor?
Replies
It shouldn't do - but how is that field being populated? Do you have a trigger listening for the edit and then it updates the table some how? That would be my suggested starting point here - disable any triggers you have on the table.
Regards,
Allan
The 'name_modelyear_brand' unique index is only to ensure we don't enter any units with a combination of the fields 'name', 'modelyear', 'brand', 'model' similar to existing entries in the database. This existed as the sole index before we added a primary key of 'unit_id' to the database for usage with the Datatables Editor PHP backend.
Perhaps the autodetect mechanism in Editor is confused over whether it should be updating a record or inserting one given the existence of this additional unique index not being the primary key? It appears to be doing both.
Thanks for your help.
Oh - sorry - I had thought it was the name of a column rather than the name of the index. Got it now!
As long that you have a unique column identifier, it doesn't really matter if it is the primary key or not - at least that should be the case.
Are you able to give me the Javascript, PHP and SQL schema for your table so I can try to replicate the issue please?
Allan
The javascript and PHP code are as per above. Below is the MYSQL schema:
Thanks for your help!
Hi,
Thanks for the SQL. I'm afraid I've not had a chance to look into this today, but I will do so early next week.
Allan
Thank you Allan. If there's anything more I can do to help troubleshoot please let me know.
Hi,
I'm really sorry for the delay on this one. I've been a bit bogged down with server issues but this is top of my list for tomorrow morning. I'll post back then.
Allan
Thanks for the SQL. I've just imported the SQL and set up a demo with your Javascript and PHP from above. The only change I've made is to comment out the
rowCallbackmethod that adds the click handlers since that has external code that I don't have.I've been able to edit a row in the table without any issue. No new rows appear (either immediately or after refresh).
The one thing that springs to mind is to wonder if the PHP libraries have also been updated to 1.5? Your comment above suggests they have, but it would be worth checking this -the client / server protocol changes with 1.5 so the old PHP libraries won't work with the new Javascript and vice-versa.
Also worth checking the Javascript version -
$.fn.dataTable.Editor.versionin your browser's console.Allan
The problem was caused by this at the head of our PHP code:
As you can see, this did not account for the record number in $_POST["data"] and was directly responsible for the additional entry.
This seemed like a harmless addition well above the Editor initialization, but it was certainly not the case.
[EDIT] Just wanted to thank you again for your time and apologize - the problem was clearly on our end.
No problem at all - good to hear you've got it sorted out! The code you have above would work well for the legacy data structure submitted by Editor, but it was always vulnerable to changes such as the one for 1.5. To address that for any potential future changes (unlikely, but can't predict the future!), 1.5 also introduced server-side events. That completely decouples the checking for values like that from the format submitted.
Allan