How to update options in 'select' field based on other field values (options data pulled from DB)?

pansengtat

Hi, I wish to modify my instance of Editor in the JS file, such that a field of type select can change its options to values created from the database, based on what value I selected or wrote in another field.

I believe that this question might be familiar like these links, however my question is abit different as I intend to withdraw values from the database using a SQL statement with a WHERE clause matching a value selected in another Editor field.

• http://datatables.net/forums/discussion/9941/change-dynamically-hidden-field-value-based-on-other-field-value (this one's not answered)
• https://datatables.net/forums/discussion/13933/update-a-field-based-on-the-value-of-another (this one's using some hard-coded values)

Like, for example, if I change the field Workgroup from selection Group 1 to Group 2, the field for Order will change all its option values, fetching from the table Orders with a SQL statement like SELECT ID, OrderNumber FROM Orders WHERE GroupID = "hidden_variable_storing_GroupID".

Currently, my JS code looks like this:

(function($){
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor({
            ajax: "php/myEditor.php",
            table: "#myTable",
            fields: [
                {
                    label: "Project: ",
                    name: "Requests.projectID",
                    type: "select"
                },{
                    label: "Workgroup: ",
                    name: "Requests.workgroupID",
                    type: "select"
                },{
                    label: "Order: ",
                    name: "Requests.orderID",
                    type: "select"
                }
            ]
        } );
     
        $('#myTable').DataTable( {
            dom: "Tfrtip",
            ajax: "php/myEditor.php",
            columns: [
                { data: "Requests.ID" },
                { data: "Projects.Name" },
                { data: "Requests.projectID" },
                { data: "Workgroups.Name" },
                { data: "Requests.orderID" }
            ],
            tableTools: {
                sRowSelect: "os",
                sSwfPath: "extensions/tabletools/swf/copy_csv_xls_pdf.swf",
                aButtons: [
                    {   sExtends:       "editor_edit",   
                        editor:         editor,
                        sButtonText:    "Update changes"
                    },
                    {   sExtends:       "xls",
                        sButtonText:    "Export as Excel",
                        sAction:        "flash_save"
                    },
                    {   sExtends:       "csv",
                        sButtonText:    "Export as CSV",
                        sAction:        "flash_save"
                    },
                    {   sExtends:       "pdf",
                        sButtonText:    "Export as PDF",
                        sPdfOrientation:"landscape"
                    },
                    "print"
                ]
            },
            "deferRender": true,
            initComplete: function(settings, json) {    // This map to show dropdown options when using 'Edit' function
                editor.field('Requests.workgroupID').update(json.Workgroups);
                editor.field('Requests.orderID').update(json.Orders);
                editor.field('Requests.projectID').update(json.Projects);
            }
        } );
    } );
}(jQuery));

Here are the modifications I attempt to make but was unsuccessful in achieving the desired effect:

$( editor.field( 'Requests.workgroupID' ).node() ).on('change', function () {
    var grpID= editor.field( 'Requests.workgroupID' ).val();
    $("#DTE_Field_Requests.orderID option").each( function ( i ) 
    {
              $("#DTE_Field_Requests.orderID")
                            .appendTo( $(this).empty() );
               
        //--- Code to retrieve ID and OrderNumber from table ORDERS via MySQL, not sure how to do this ---//
        {
            $("#DTE_Field_Requests.orderID").append( '<option value="'+id+'">'+orderNumber+'</option>' );
        } );
    } );
});

allan

Your solution looks close, but I would suggest using the update() method rather than trying to manipulate the select element's DOM directly. Also if you want to make an Ajax request to get the data, just use $.ajax:

$( editor.field( 'Requests.workgroupID' ).node() ).on('change', function () {
    var grpID= editor.field( 'Requests.workgroupID' ).val();

    $.ajax( {
        url: '...',
        data: {
            grpID: grpID
        },
        dataType: 'JSON',
        success: function ( json ) {
            editor.field( 'Requests.orderID' ).update( json );
        }
    } );
} );

Assuming of course that the JSON returns a format that the Editor select field type supports. If not you could process the data as needed.

Allan

pansengtat

Hi @allan,

This is the server-side script where I attempt to get the values to re-populate that field inside the JS file, but I don't know if there is a way to insert a WHERE clause when forming the select/options.

$tempGroupID = $_POST["grpID"];

$data = Editor::inst($db, 'Orders', 'ID')
        ->where('Orders.workgroupID', $tempGroupID)
        ->field(
            Field::inst('Orders.ID')->set(false),
            Field::inst('Orders.OrderNum')->set(false)
        )
        ->process($_POST)
        ->data();
     
    if ( !isset($_POST['action']) ) {
        $data['Orders'] = $db
            ->selectDistinct('Orders', 'ID as value, OrderNum as label')
            ->fetchAll();       
    }
    echo json_encode($data);

allan

Yes, the selectDistinct documentation shows that the third parameter can be used a query condition.

For complete control, you could do:

$data['Orders'] = $db
  ->query( 'select' )
  ->table( 'Orders' )
  ->get( 'ID as value' )
  ->get( 'OrderNum as label' )
  ->where( ... )
  ->exec()
  ->fetchAll();

That provides more control over the where options as you can use or_where etc.

Allan