Editor with 3 tables

Rappi

Hello.

I have 3 tables:

First (tm_spezialgebiete):

id | Name
---------------
1 | gebiet 1
2 | gebiet 2
...

Second (tm_arzt):

id | Name
------------
1 | arzt 1
2 | arzt 2
....

and a third wich bring the data together (tm_arzt_gebiet):

id | arzt | gebiet
----------------------
1 |     2  |    1
2 |     2  |    2
3 |     1  |    1
....

My JS

(function($){

$(document).ready(function() {

    
    var editor = new $.fn.dataTable.Editor( {
        "ajax": "arzt_gebiete_processing.php?xid=<?php echo $xid; ?>",
        "table": "#tm_gebiete",
        "fields": [
            {
                "label": "Spezialgebiet",
                "name": "tm_spezialgebiete.Name",
                "type": "select"
            }
        ],
        i18n: {
            create: {
                button: "Neu",
                title:  "Neuer Eintrag",
                submit: "Erstellen"
            },
            edit: {
                button: "Edit",
                title:  "Eintrag editieren",
                submit: "Speichern"
            },
            remove: {
                button: "Löschen",
                title:  "Eintrag löschen",
                submit: "Löschen",
                confirm: {
                    _: "Wollen Sie wirklich %d Zeilen löschen?",
                    1: "Wollen Sie den Eintrag wirklich löschen?"
                }
            }
         }
    } );

    $('#tm_gebiete').DataTable( {
        
    language: {
            buttons: {
                colvis: "Anzuzeigende Spalten",
                colvisRestore: "Zurücksetzen"
            },
            paginate: {
                first:      "Erste",
                last:       "Letzte",
                next:       "Weiter",
                previous:   "Zurück"
            },
            search: "Suchen:",
            info: "Zeige _START_ bis _END_ von _TOTAL_ Einträgen",
            infoEmpty:      "Zeige 0 bis 0 von 0 Einträgen",
            decimal:        ",",
            thousands:      "."
        },
    "sPaginationType":"full_numbers",
        "dom": "B<'clear'>rtip",
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ],
        "select": true,
        "ajax": "arzt_gebiete_processing.php?xid=<?php echo $xid; ?>",
        "columns": [
            
            {
                "data": "tm_spezialgebiete.Name",
                "editField": "tm_spezialgebiete.Name"
            }
        ]
    } );
} );

}(jQuery));                 

My PHP script

<?php
require_once("models/config.php"); 

$xid = $_GET["xid"]; 
/*
 * Editor server script for DB table arzt
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "classes/DataTables.php" );

$db->sql("SET character_set_client=utf8");
$db->sql("SET character_set_connection=utf8");
$db->sql("SET character_set_results=utf8");

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tm_arzt_gebiet', 'id' )
    ->fields(
        Field::inst( 'tm_arzt_gebiet.id' ),
        Field::inst( 'tm_arzt_gebiet.Arzt' )
            ->options( 'tm_arzt', 'id', 'Praxis' ),
        Field::inst( 'tm_arzt.Praxis' ),
        
        Field::inst( 'tm_arzt_gebiet.Gebiet' )
            ->options( 'tm_spezialgebiete', 'id', 'Name' ),
        Field::inst( 'tm_spezialgebiete.Name' )
        
    )
    ->where('tm_arzt_gebiet.Arzt', $xid, '=')
    ->leftJoin( 'tm_arzt', 'tm_arzt.id', '=' , 'tm_arzt_gebiet.Arzt' )
    ->leftJoin( 'tm_spezialgebiete', 'tm_spezialgebiete.id', '=' , 'tm_arzt_gebiet.Gebiet' )
    
    
    ->process( $_POST )
    ->json();

The table show correct but the editor don't show any entry in the select field.

How can I show the entrys from tm_spezialgebiete and save it with the correct id from 'tm_arzt' to tm_arzt_gebiet ?

Rappi

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Rappi

Now I have read

https://editor.datatables.net/examples/advanced/joinLinkTable.html
https://editor.datatables.net/examples/advanced/joinArray.html

sometimes.

I think there is the answer, but I don't understand it :-(

Any help?

allan

You want to edit the value of tm_arzt_gebiet.Gebiet with values from tm_spezialgebiete.id. As such, your Editor field name should be tm_arzt_gebiet.Gebiet since that is the column you want to change the value of on the database when the data is submitted.

The editField value needs to be updated to match that, and then I think it should work.

It might be worth reading through the join manual as well.

Allan

Rappi

Ok. Thanks Allan.
The first thing is working ;-)

I have change the Editor field to tm_arzt_gebiet.Gebiet and now the change and delete is working.

But how can I insert a new entry from the list?
I must set the table tm_arzt_gebiet.Arzt entry too and I don't know what I must insert and where.... In my JS? Or in the PHP script?

Rappi

Rappi

Join was not my friend :-(

But I have found a solution that work for me :-D

In the editor I have:

"fields": [
            {
                "label": "Arzt",
                "name": "tm_arzt_gebiet.Arzt",
                "def": "<?php echo $xid; ?>",
                "type": "hidden"
            
            },
            {
                "label": "Spezialgebiet",
                "name": "tm_arzt_gebiet.Gebiet",
                "type": "select"
            }
        ],

and now the ID is saving AND the field will not show.

That's what I want.

Rappi

allan

Thanks for posting back. Good to hear you've got it working as you need.

Allan