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

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

johnhpejohnhpe Posts: 12Questions: 3Answers: 0

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();
?>

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;

Answers

  • johnhpejohnhpe Posts: 12Questions: 3Answers: 0

    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???????

  • johnhpejohnhpe Posts: 12Questions: 3Answers: 0

    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();
    ?>
    
  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Excellent, glad all sorted, thanks for reporting back,

    Colin

This discussion has been closed.