Inline editing with joined tables is deleting data

richardvk

Hi,

I have two database tables, left and right, with a one-to-many mapping (ie each row in the left table has multiple mapped values in the right table).

In the editor ajax (php script), i do a join, using an array to hold the multiple values, which are shown as comma separated in the actual displayed table (similar to this example: https://editor.datatables.net/examples/advanced/joinArray.html where the Access column has multiple, comma separated values).

The problem is i am using inline editing ONLY for the first column, but when i submit, all the corresponding data for that entry in the right table is being deleted?!?

I have enabled general logging in MySQL and confirm that there is a DELETE statement being issued by Editor. Not sure why?

• I have tried setting '->set( false )' on all the fields being selected from the right table in the JOIN statement, no joy...
• I have created a separate section in the ajax php file "if ($_POST['action'] == 'edit'){" which does NOT do a join but only has a definition for the first table's data - this WORKS for the update, but throws javascript errors as the return data is not matching up with the data originally specified by the original table create (i hope this makes sense!)

Please help! Is such an inline edit on one-to-many joined tables even possible?

allan

Hi,

Are you able to show me your PHP code? However, the DELETE statement suggests that you have a link table - is that correct? The Editor PHP libraries do a DELETE when updating the link table, but should then do an INSERT to recreate the links. It would be much better to do some kind of diff and only delete and insert rows that need to be removed and added, rather than just doing it blindly - but that adds significant extra complexity. I'll add it one day, but it isn't available yet!

Is such an inline edit on one-to-many joined tables even possible?

Yes - as an example - using the example you linked to if you open your browser's console and enter the following it should work okay:

$('#example').on( 'click', 'tbody td:first-child', function () {
  editor.inline( this );
} );

$('#example').on( 'click', 'tbody td:last-child', function () {
  editor.inline( this, 'access[].id', { buttons: 'Save' } );
} );

The first block will make the first column in the table inline editable. The second block the last column. In both cases it is possible to inline edit and the one-to-many relationship is preserved.

So it should be possible, but something is obviously going wrong somewhere for your use case. I think I would need more information before being able to help though. The PHP and ideally a link to the page would be great.

Thanks,
Allan

richardvk

Hi Allan, thanks as always for your prompt response!

This is not a link table scenario, just the two directly referenced tables, so maybe there is a problem on my side. My work is all on internal work servers so i would need to put up a demo somewhere where its publicly accessible, but before i do that let me revisit based on your advice and comments.

Knowing where the DELETE fits in, and that this is actually possible is already very helpful :)