When using EDITOR to UPDATE or INSERT with a JOIN no data is updated

johnhpe

There are no errors, I have tried the latest 2.0.0, running PHP server running MYSQL. If I remove the JOIN it works, I have copied the example in the pages to the letter but still no joy. Debug of the MYSQL shows that when an EDIT is performed (correct data in header - see below) only a SELECT on this row is performed with NO UPDATE :-(

Javascript:

$(document).ready(function() {
        
        var editor;
        
        editor = new $.fn.dataTable.Editor( {
            ajax: "ajax/manSubSports.php",
            table: "#example",
            fields: [ 
                {
                    label: "Name:",
                    name: "sub_sports.name"
                }, {
                    label: "Parent Sport:",
                    name: "sub_sports.sport_id",
                    type: "select"
                }, {
                    label: "Max no. of Players",
                    className: "widthAuto",
                    name: "sub_sports.sub_max_no_players",
                    type: "select",
                    seperator: ',',
                    options: [
                        { label: 'Use Main Sport Value', value: 0 },
                        { label: '1', value: 1 },
                        { label: '2', value: 2 },
                        { label: '3', value: 3 },
                        { label: '4', value: 4 }
                    ]
                }
            ],
        } );
        
        $('#example').DataTable( {
            ajax: {url :"ajax/manSubSports.php", type: "POST"},
            dom: 'Blfrtip',
            select: true,
            lengthMenu: [ [20, 50, 100, -1], [20, 50, 100, "All"] ],
            pageLength: 20,
            processing: true,
            serverSide: true,
            "order": [1,'asc'],
            columns: [ 
                {data: "sub_sports.name","width":"220px","searchable":true,"orderable":true},
                {data: "sports.sport","width":"220px","searchable":true,"orderable":true},
                {data: "sub_sports.sub_max_no_players","searchable":true,"orderable":true}
            ],
            select: true,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit', editor: editor },
                { extend: "remove", editor: editor, formMessage: function( e, dt ){ return 'Are you sure you want to delete the selected '+dt.rows({selected:true}).count()+' queries?'; } }
            ]
        });

HTML:

<table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
                <thead><tr><th class="thLeft">Sub Sport</th><th class="thLeft">Parent Sport</th><th>Max No. of Players</th></tr></thead>
            </table>

Server Side:

<?php
session_start(['name'=>'EBOADMIN']);

include( "../JS/Editor-PHP-2.0.0/lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

$database = 'bookingdb'.$_SESSION['id'];

$editor = Editor::inst( $db, $database.'.sub_sports','sub_id')
    ->debug(true)
    ->field(
        Field::inst( 'sub_sports.name' )->validator(Validate::required()),
        Field::inst( 'sub_sports.sport_id' )
            ->options( Options::inst()
                ->table( $database.'.sports' )
                ->value( 'id' )
                ->label ( 'sport' )
            ),
        Field::inst( 'sports.sport' ),
        Field::inst( 'sub_sports.sub_max_no_players' )
    )
    ->leftJoin( $database.'.sports', 'sports.id', '=', 'sub_sports.sport_id')
    ->process($_POST)
    ->json();

<?php
>
```
?>


Header on EDIT

action: edit
data[row_32][sub_sports][name]: MYSQL
data[row_32][sub_sports][sport_id]: 1
data[row_32][sub_sports][sub_max_no_players]: 2

JSON Response:

{"data":[{"DT_RowId":"row_32","sub_sports":{"name":"MYSQL","sport_id":"1","sub_max_no_players":"4"},"sports":{"sport":"Tennis"}}],"debug":[{"query":"SELECT bookingdb1.sub_sports.sub_id as 'bookingdb1.sub_sports.sub_id', sub_sports.name as 'sub_sports.name', sub_sports.sport_id as 'sub_sports.sport_id', sports.sport as 'sports.sport', sub_sports.sub_max_no_players as 'sub_sports.sub_max_no_players' FROM bookingdb1.sub_sports LEFT JOIN bookingdb1.sports ON sports.id = sub_sports.sport_id WHERE bookingdb1.sub_sports.sub_id = :where_0 ","bindings":[{"name":":where_0","value":"32","type":null}]}]}

Been struggling to get this to work for a day now, just in case here is the structure of the two tables:

CREATE TABLE sub_sports (
name varchar(16) NOT NULL,
sub_id smallint(6) UNSIGNED NOT NULL,
sport_id smallint(6) UNSIGNED NOT NULL,
sub_max_no_players tinyint(3) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

-- Dumping data for table sub_sports

INSERT INTO sub_sports (name, sub_id, sport_id, sub_max_no_players) VALUES
('Racket Ball', 16, 2, 1),
('MYSQL', 32, 1, 4);

--

-- Indexes for dumped tables

--

-- Indexes for table sub_sports

ALTER TABLE sub_sports
ADD PRIMARY KEY (sub_id),
ADD KEY sport_id (sport_id);
COMMIT;
```

johnhpe

So I thought I got burnt by this one before and here was the thread that I updated myself with the fix / workaround:

https://datatables.net/forums/discussion/57061/issue-in-getting-join-to-work-with-editor#latest

The issue is still there in the latest release in that if you present the database name in the Editor instantiation then JOINs just don't work.

However I NEED to present the database name as it changes so can anyone let me know how to change it dynamically. I know it is registered in the config.php file but I need the ajax script to update it???????

johnhpe

So I think I have answered my own question again. Looking at bootstrap.php if the variable $sql_details exists it is not overwritten. So declaring it before calling Editor is the fix.....

```
<?php
session_start(['name'=>'EBOADMIN']);

$database = 'bookingdb'.$_SESSION['id'];

$sql_details = array(
"type" => "Mysql", // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
"user" => "********", // Database user name
"pass" => "*********", // Database password
"host" => "localhost", // Database host
"port" => "", // Database connection port (can be left empty for default)
"db" => $database, // Database name
"dsn" => "", // PHP DSN extra information. Set as charset=utf8mb4 if you are using MySQL
"pdoAttr" => array() // PHP PDO attributes array. See the PHP documentation for all options
);

include( "../JS/Editor-PHP-2.0.0/lib/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'sub_sports','sub_id')
->debug(true)
->field(
Field::inst( 'sub_sports.name' )->validator(Validate::required()),
Field::inst( 'sub_sports.sport_id' )
->options( Options::inst()
->table( $database.'.sports' )
->value( 'id' )
->label ( 'sport' )
),
Field::inst( 'sports.sport' ),
Field::inst( 'sub_sports.sub_max_no_players' )
)
->leftJoin( 'sports', 'sports.id', '=', 'sub_sports.sport_id')
->process($_POST)
->json();

<?php > ``` ?>

colin

Excellent, glad all sorted, thanks for reporting back,

Colin