Dependent field - Cascading lists in Editor

mihalisp

Hi,i have an editor with the following fields

    var editor = new $.fn.dataTable.Editor( {
          ajax: "Editor_PHP/controllers/editor.php",
          table: "#example",
          fields: [ {
            label: "Username:",
            name: "leaves_table.emp_id",
            type: "select"
        }, {
            label: "Surname:",
            name: "Employees.emp_last"
        }, {
            label: "Name:",
            name: "Employees.emp_first"
        }, {
            label: "Leave Type:",
            name: "leaves_table.leave_id",
            type: "select"
             } //.............
          ]
          } );

The php backend file is:

     <?php
       include( "../lib/DataTables.php" );

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

     Editor::inst( $db, 'leaves_table' ,'leave_id')
    ->field(
    Field::inst( 'leaves_table.emp_id' )
           ->options( Options::inst()
                     ->table( 'Employees' )
                     ->value( 'emp_id' )
             ->label( 'emp_username' )   )
        ->validator( Validate::dbValues() ), // 1st Question-->.why do i need this??

    Field::inst( 'Employees.emp_id' ),
    Field::inst( 'Employees.emp_username' ),
    Field::inst( 'Employees.emp_last' ),
    Field::inst( 'Employees.emp_first' ),
    Field::inst( 'leaves_table.leave_id' )
                   ->options( Options::inst()
                            ->table( 'leaves_type' )
                            ->value( 'leave_id' )
            ->label( 'leave_descr' )   )
            ->validator( Validate::dbValues() ) ,

    Field::inst( 'leave_type.tadies_descr' ),
          ->getFormatter( Format::dateSqlToFormat( 'Y' ) )
  )

->leftJoin( 'leaves_type',     'leaves_table.tadies_id',          '=', 'leaves_type.leave_id' )
->leftJoin( 'Employees', 'Employees.emp_id',          '=', 'leaves_table.emp_id' )
->process($_POST)
->json();

1)Why do i need this?? --->validator( Validate::dbValues() ).
Can you give me an example?

2) Maybe a silly question,Instead of

         Editor::inst( $db, ...........
          ...............................
            ->json();

can i use the easier way of Php SQL code "SELECT ... FROM...LEFT JOIN...WHERE.." ??
Is the ssp.class.php an alternative to use in Editor?

3) In my above Editor,i want the emp_username to be a dependent() field and when i change it ,i want it to update dynamically the other 2 fields (Employees.emp_last,Employees.emp_first).I am a little bit confused using the following,can you give me some piece of code please?I believe that it is a very common problem when using cascading lists.

I am not sure how to use this i saw here--> https://datatables.net/forums/discussion/33275/i-want-to-populate-a-select-fields-options-dynamically-from-a-mysql-db

     editor.dependent( 'PermissionToEnter.unit', function (val, data, callback) {
     var test= new Array({"label" : "a", "value" : "a"});
     $.ajax({
     url: document.location.origin+'/Nexus/php/GetUnits.php',
     dataType: 'json',
     success: function (json) {
        for(var a=0;a < json.length;a++){
            obj= { "label" : json[a][0], "value" : json[a][1]};
            test.push(obj);
        }
        callback(test);
       }
      });
     });

or this from here --> https://datatables.net/blog/2017-09-01

    $countries = $db
      ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
      ->fetchAll();

       echo json_encode( [
      'options' => [
      'country' => $countries
    ]
    ] );

Thank you.

allan

1)Why do i need this?? --->validator( Validate::dbValues() ).

You don't, but it is good practice to validate the data submitted by the end user. Otherwise they would write anything to the db.

can i use the easier way of Php SQL code "SELECT ... FROM...LEFT JOIN...WHERE.." ?? Is the ssp.class.php an alternative to use in Editor?

Sure - you can use that. But that would only work for the SELECT. You'd need to program options for the CREATE, UPDATE and DELETE as well. The Editor PHP libraries are provided to make all that sort of stuff easier.

3

What are your fields you want to update? In the example code you show about it will update the list of options for the country. Add other properties to that array if you want other field options to be updated.

Allan

mihalisp

Thanks Allan,
when the 'emp_username' changes , the other 2 fields Last Surname,Name-->('Employees.emp_last','Employees.emp_first') will be filled-updated dynamically.

I think i have to use something like this:

   editor.dependent( 'Employees.emp_username', function (val, data, callback) {
   var test= new Array({"label" : "a", "value" : "a"});  //Why "a" ??
   $.ajax({
       url: ............php',
       dataType: 'json',
       success: function (json) {
                        for(var a=0;a < json.length;a++){
                              obj= { "label" : json[a][0], "value" : json[a][1]};
                                         test.push(obj);
                                             }
                         callback(test);
            }
        });
    });

and this

       //where should i put this piece of code?
       $countries = $db
       ->select( 'Employees', ['emp_id as value', 'emp_last as label'], ['emp_username' =>         $_REQUEST['values']['emp_username']] ) // 
       ->fetchAll();

       echo json_encode( [
      'options' => [
       'emp_last' => $countries
     ]
   ] );

Thanks

mihalisp

Can you please look into my las comment for some help??

I can't make it work.
Thanks.

allan

Sorry I missed that one.

I don't think you need that $.ajax() call at all - just let dependent() do the Ajax call for you:

editor.dependent( 'Employees.emp_username', 'whatever.php' );

Then have whatever.php contain the options code you posted above (i.e. read it from the db and send it back to the client).

Allan