Dependant selects

Dependant selects

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
edited March 2020 in Editor

Is it possible to have a dependant select based on the value of another select in the editor?

Scenario:

In the editor, there is a select for units (academic units of study...). Also in the editor is a select for unit_outcomes. Unit outcomes select needs to be populated based on the unit select option value.

Editor code:

 <div class='table_container'>
    <table id='assessment_table' class='display' style="width:100%">
      <thead>
        <tr>
          <th>Assessment</th>
          <th>Unit</th>
          <th>Weighting</th>
          <th>Assessment Type</th>
          <th>Unit Outcome</th>
          <th>Modified</th>
          <th>Modified By</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>
  </div>
  <div id="assessment_form">
    <editor-field name="assessment.assessment"></editor-field>
    <editor-field name="assessment.unit_fk"></editor-field>
    <editor-field name="assessment.weighting"></editor-field>
    <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
    <editor-field name="unit_outcome[].unit_outcome_pk"></editor-field>
  </div>
</div>
<script type="text/javascript">
        var editor; // use a global for the submit and return data rendering in the examples

        $( document ).ready( function () {


            $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/assessment_data.php",
                table: "#assessment_table",
                "autoWidth": true,
                template: '#assessment_form',
                fields: [ {
                    label: "Assessment:",
                    name: "assessment.assessment",
                    type: "ckeditor"
                }, {
                    label: "Unit:",
                    name: "assessment.unit_fk",
                    type: "select"
                }, {
                    label: "Weighting:",
                    name: "assessment.weighting"
                }, {
                    label: "Assessment type:",
                    name: "assessment_type[].assessment_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Unit Outcome:",
                    name: "unit_outcome[].unit_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );


            var table = $( '#assessment_table' ).DataTable( {
                responsive: true,
                ajax: "program_data/assessment_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "assessment.assessment"
                }, {
                    data: "unit.unit_name"
                }, {
                    data: "assessment.weighting"
                }, {
                    data: "assessment_type",
                    render: "[, ].assessment_type"
                }, {
                    data: "unit_outcome",
                    render: "[, ].unit_outcome"
                }, {
                    data: "assessment.modified"
                }, {
                    data: "assessment.modified_by"
                } ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: [ ]
            } );

Data code:


Editor::inst( $db_cm_md, 'assessment', 'assessment_pk' ) ->field( Field::inst( 'assessment.assessment' ), Field::inst( 'assessment.weighting' ), Field::inst( 'assessment.modified' ), Field::inst( 'assessment.modified_by' )->setValue( $user ), Field::inst( 'assessment.unit_fk' ) ->options( Options::inst() ->table( 'unit' ) ->value( 'unit_pk' ) ->label( 'unit_name' ) ), Field::inst( 'unit.unit_name' ) ) ->leftJoin( 'unit', 'unit.unit_pk', '=', 'assessment.unit_fk' ) ->join( Mjoin::inst( 'assessment_type' ) ->link( 'assessment.assessment_pk', 'assessment_assessment_type_lookup.assessment_fk' ) ->link( 'assessment_type.assessment_type_pk', 'assessment_assessment_type_lookup.assessment_type_fk' ) ->order( 'assessment_type.assessment_type asc' ) ->fields( Field::inst( 'assessment_type_pk' ) ->options( Options::inst() ->table( 'assessment_type' ) ->value( 'assessment_type_pk' ) ->label( 'assessment_type' ) ), Field::inst( 'assessment_type' ) ) ) ->join( Mjoin::inst( 'unit_outcome' ) ->link( 'assessment.assessment_pk', 'unit_outcome_assessment_lookup.assessment_fk' ) ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_assessment_lookup.unit_outcome_fk' ) ->order( 'unit_outcome.unit_outcome asc' ) ->fields( Field::inst( 'unit_outcome_pk' ) ->options( Options::inst() ->table( 'unit_outcome' ) ->value( 'unit_outcome_pk' ) ->label( 'unit_outcome' ) ), Field::inst( 'unit_outcome' ) ) ) ->process($_POST) ->json();

e.g. where:

unit_outcome.unit_fk = unit.unit_pk in the lookup table:

CREATE TABLE IF NOT EXISTS `unit_unit_outcome_lookup` (
  `unit_unit_outcome_lookup_pk` int(10) NOT NULL AUTO_INCREMENT,
  `unit_outcome_fk` int(10) NOT NULL,
  `unit_fk` int(3) NOT NULL,
  PRIMARY KEY (`unit_unit_outcome_lookup_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=262 ;

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    OK, found out that dependant selects are available:

    https://editor.datatables.net/examples/api/dependentFields

    Now my question:

    How do I pass the value from the editor form field below to the PHP script for the dependant select query using:

    <div id="assessment_form">
        <editor-field name="assessment.assessment"></editor-field>
        <editor-field name="assessment.unit_fk"></editor-field>
        <editor-field name="assessment.weighting"></editor-field>
        <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
        <editor-field name="unit_outcome[].unit_outcome_pk"></editor-field>
      </div>
    </div>
    <script type="text/javascript">
            var editor; // use a global for the submit and return data rendering in the examples
    
            $( document ).ready( function () {
    
                var editor = new $.fn.dataTable.Editor( {                   
                    ajax: "program_data/assessment_data.php",
                    table: "#assessment_table",
                    "autoWidth": true,
                    template: '#assessment_form',
                    fields: [ {
                        label: "Assessment:",
                        name: "assessment.assessment",
                        type: "ckeditor"
                    }, {
                        label: "Unit:",
                        name: "assessment.unit_fk",
                        type: "select"
                    }, {
                        label: "Weighting:",
                        name: "assessment.weighting"
                    }, {
                        label: "Assessment type:",
                        name: "assessment_type[].assessment_type_pk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        multiple: true
                    }, {
                        label: "Unit Outcome:",
                        name: "unit_outcome[].unit_outcome_pk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        multiple: true
                    }]
                } );
                
                
                editor.dependent( 'unit_outcome[].unit_outcome_pk', 'program_data/get_unit_outcomes_data.php' );
    
    
                var table = $( '#assessment_table' ).DataTable( {
                    responsive: true,
                    ajax: "program_data/assessment_data.php",
                    dom: "Blfrtip",
                    columns: [ {
                        data: "assessment.assessment"
                    }, {
                        data: "unit.unit_name"
                    }, {
                        data: "assessment.weighting"
                    }, {
                        data: "assessment_type",
                        render: "[, ].assessment_type"
                    }, {
                        data: "unit_outcome",
                        render: "[, ].unit_outcome"
                    }, {
                        data: "assessment.modified"
                    }, {
                        data: "assessment.modified_by"
                    } ],
                    select: {
                        style: 'os',
                        selector: 'td:first-child'
                    },
                    buttons: [ ]
                } );
    

    PHP for dependant select for unit_outcome[].unit_outcome_pk field:

    $unit = $_POST['unit'];
    
    $data = array();
    
    $query = "SELECT * FROM unit, unit_outcome, unit_unit_outcome_lookup WHERE unit_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk AND unit_unit_outcome_lookup.unit_fk = unit.unit_pk AND unit.unit_pk = '$unit'";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("value"=>$row['unit_outcome_pk'], "option"=>$row['unit_outcome']);
    }
    
    echo json_encode($data);
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    Or, how could the following be used with a lookup table (as advised above) and passing the value of 'unit_pk' to the query?

    https://datatables.net/blog/2017-09-01

    include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );
     
    $countries = $db
        ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
        ->fetchAll();
     
    echo json_encode( [
        'options' => [
            'country' => $countries
        ]
    ] );
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    Note: Not sure if I have the JSON output structure correct either, it may have to be in this format:

    $unit = $_POST['unit']
    
    $data = array();
    
    $query = "SELECT * FROM unit, unit_outcome, unit_unit_outcome_lookup WHERE unit_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk AND unit_unit_outcome_lookup.unit_fk = unit.unit_pk AND unit.unit_pk = '$unit'";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        array_push( $data, array('label'=>$row['unit_outcome_pk'], 'value'=>$row['unit_outcome']) );
    }
    
    $temp = array('unit_outcomes'=>$data);
    $json = array('options'=>$temp);
    echo json_encode($json);
    

    Docs could be improved with some real examples

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    Hardcoding the value of $unit produces:

    {"options":{"unit_outcomes":[{"label":"1","value":"
    
    Display professional behaviour in the educational and clinical settings and outline some challenges to professionalism<\/p>"},{"label":"2","value":"
    
    Outline the principles of reflective practice, recognise personal abilities and difficulties and access support services when necessary<\/p>"},{"label":"4","value":"
    
    Outline the factors affecting team and group roles, structure, function and goals; and work effectively in a learning group<\/p>"},{"label":"5","value":"
    
    Outline the benefits of interprofessional practice<\/p>"},{"label":"6","value":"
    
    Outline the organisation of the health care system and its delivery in Australia including the roles of doctors and medical students<\/p>"},{"label":"7","value":"
    
    Outline the basic principles of health advocacy and their application to special and specific needs of individuals, groups, communities and populations<\/p>"},{"label":"8","value":"
    
    Explain the impact of historical, geographical and socio-cultural factors on the health and health care of Aboriginal people and communities, and the elements of cultural security for Aboriginal people<\/p>"},{"label":"9","value":"
    
    Outline the factors contributing to the health and health disparities of individuals, groups and communities including diverse and vulnerable groups, and underserved populations<\/p>"},{"label":"10","value":"
    
    Outline principles, strategies and controversies in health maintenance, promotion, screening and disease prevention<\/p>"},{"label":"11","value":"
    
    Explain generic principles of clinically relevant normal and abnormal human structure, function, behaviour, development, responses and compensatory mechanisms to illness and injury, and outline the classification, epidemiology, aetiology, anatomy, pathophysiology, common clinical and pathological manifestations, natural history, diagnostic principles and therapeutic principles for some specified organ systems and medical conditions<\/p>"},{"label":"12","value":"
    
    Explain the elements of the medical consultation and physical examination and demonstrate an organised approach to taking a medical history and performing physical examination, explain the principles of clinical reasoning<\/p>"},{"label":"13","value":"
    
    Explain generic principles of patient management including pharmacological and non-pharmacological therapies, and explain the use of therapies for specified organ system medical conditions, and demonstrate adherence to infection control and safe patient handling<\/p>"},{"label":"14","value":"
    
    Outline the issues related to the patient's perspectives of health, illness and healthcare experiences<\/p>"},{"label":"15","value":"
    
    Demonstrate appropriate bioscientific vocabulary and display professional, concise and accurate oral, written and electronic biomedical communication skills<\/p>"},{"label":"16","value":"
    
    Define the elements of quality care (safety, efficacy, efficiency, timeliness, patient--centredness, equity) and medical error<\/p>"},{"label":"17","value":"
    
    Outline principles of learning, identify personal learning needs, implement a personal learning plan and effectively use appropriate educational resources<\/p>"},{"label":"18","value":"
    
    Outline effective approaches to developing mentoring relationships from the mentee perspective<\/p>"},{"label":"19","value":"
    
    Outline principles of patient health literacy and sources of health information available to patients<\/p>"},{"label":"20","value":"
    
    Explain adult learning educational principles<\/p>"},{"label":"21","value":"
    
    Outline the principles of educational assessment and evaluation and effectively respond to constructive feedback<\/p>"},{"label":"22","value":"
    
    Outline the principles of the scientific method, research study designs, and biostatistics<\/p>"},{"label":"23","value":"
    
    Outline the principles of evidence-based practice and evidence-based processes, tools and systems<\/p>"},{"label":"24","value":"
    
    Evaluate and select reliable, efficient and authoritative sources of medical information to support learning<\/p>"}]}}
    
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    That looks about 99% correct - I think the only thing wrong is that the JSON returned from the server does not reference a field that exists in your client-side Editor. Specifically this array('unit_outcomes'=>$data); I think should reference:

    unit_outcome[].unit_outcome_pk
    

    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Not sure that I follow Allan, could you provide more info, example?

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Ah. I think I see now. The json elements under 'unit_outcomes' need to match the name of the field, which should be changed to 'unit_outcomes'.

    Not particularly clear from the start, but makes sense now. Alot of this could be made more explicit in the 'docs'!

    So, now I have the 'unit_outcomes' select populated through hard coding the $unit variable. But how do I pass the value of the 'unit' (name: "assessment.unit_fk",) select that 'unit_oucomes' is dependant on?

    Also, in the dependant menu, I have just the unit_outcome_pk displayed, not the unit_outcome itself.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    OK, I switched the value around in the JSON. now I get a whopping great space in the editor between the editor labels and the fields:

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Sort of solved that by striping the para tags and truncatiing the length of the options in the select:

    $data[] = array("label"=>substr(strip_tags($row['unit_outcome']), 0, 100), "value"=>$row['unit_outcome_pk']);
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Still looking for a solution to pass the value of assessment.unit_fk select through:

    editor.dependent( 'unit_outcomes', 'program_data/get_unit_outcomes.php);
    

    and will this be triggered by change to assessment.unit_fk select??

    Really need a solution to this which I thought dependant() would provide.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    OK, managed to get the 'unit_outcomes' select populated from the 'assessment.unit_fk' select using the following code. However, I can't select an option from the 'unit_outcomes' select and it appears to be jittery (reloading???). The top option in the list keeps flashing with the highlight.

    <div id="assessment_form">
        <editor-field name="assessment.assessment"></editor-field>
        <editor-field name="assessment.unit_fk"></editor-field>
        <editor-field name="assessment.weighting"></editor-field>
        <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
        <editor-field name="unit_outcomes"></editor-field>
      </div>
    </div>
    <script type="text/javascript">
            var editor; // use a global for the submit and return data rendering in the examples
    
            $( document ).ready( function () {
    
                var program = $( '#program_field' ).val();
                var user = $( '#user_field' ).val();
                var permission = $( '#permission_field' ).val();
                
    
    
                $( '#main-menu' ).smartmenus();
    
                $( '#program_levels' ).change( function ( e ) {
                    window.location.href = 'edit_' + this.value + ".php?program=" + program + '&user=' + user;
                } );
    
    
                $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;
    
                var editor = new $.fn.dataTable.Editor( {                   
                    ajax: "program_data/assessment_data.php",
                    table: "#assessment_table",
                    "autoWidth": true,
                    template: '#assessment_form',
                    fields: [ {
                        label: "Assessment:",
                        name: "assessment.assessment",
                        type: "ckeditor"
                    }, {
                        label: "Unit:",
                        name: "assessment.unit_fk",
                        type: "select"
                    }, {
                        label: "Weighting:",
                        name: "assessment.weighting"
                    }, {
                        label: "Assessment type:",
                        name: "assessment_type[].assessment_type_pk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        multiple: true
                    }, {
                        label: "Unit Outcome:",
                        name: "unit_outcomes",
                        type: "select"
                    }]
                } );
    
                
                editor.dependent('unit_outcomes', function(val, data, callback){
                $.ajax ( {
                    url: 'program_data/get_unit_outcomes.php',
                    data: {
                           "unit": editor.get( 'assessment.unit_fk' )
                           },
                    type: 'POST',
                    dataType: 'JSON',
                    success: function ( JSON ) {
                       callback( JSON );
                      }
                });
            }) ;
    
                var table = $( '#assessment_table' ).DataTable( {
                    responsive: true,
                    ajax: "program_data/assessment_data.php",
                    dom: "Blfrtip",
                    columns: [ {
                        data: "assessment.assessment"
                    }, {
                        data: "unit.unit_name"
                    }, {
                        data: "assessment.weighting"
                    }, {
                        data: "assessment_type",
                        render: "[, ].assessment_type"
                    }, {
                        data: "unit_outcome",
                        render: "[, ].unit_outcome"
                    }, {
                        data: "assessment.modified"
                    }, {
                        data: "assessment.modified_by"
                    } ],
                    select: {
                        style: 'os',
                        selector: 'td:first-child'
                    },
                    buttons: [ ]
                } );
    
    

    Very frustrating to say the least!

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    Note that I only want the Ajax content in the dependant to be refreshed when the parent select of the dependant select is changed. At the moment, due to constant refresh of the dependant options, I can't even select an option in the dependant.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    OK, now using:

    editor.dependent('unit_outcomes', function(val, data, callback){
    
    editor.field('assessment.unit_fk').input().on('change', function (e, d) {
    
    //check if user or editor initiated update
       if ( d && d.editorSet ) return;
                $.ajax ( {
                    url: 'program_data/get_unit_outcomes.php',
                    data: {
                           "unit": editor.get( 'assessment.unit_fk' )
                           },
                    type: 'POST',
                    dataType: 'JSON',
                    success: function ( data ) {
                       callback( data );
                      }
                });
            }) ;
    

    This solves the refresh problem with the Ajax content. :)

    Problem now is that I get a system error: :(

    Notice: Uninitialized string offset: 0 in /var/www/html/curriculum_mapper/datatables/lib/Editor/Join.php on line 782 {"data":[{"DT_RowId":"row_5","assessment":{"assessment":"
    
    test<\/p>","weighting":"10%","modified":"2020-03-10 15:18:29","modified_by":"00082563","unit_fk":"2"},"unit":{"unit_name":"Integrated Medical Sciences 2"},"assessment_type":[],"unit_outcome":[]}]}
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited March 2020

    Not getting the error now , not sure why.

    However, just realised that the following fields in the assessment editor aren't being added to their lookup tables, this was working OK before. Otherwise, the assessment is being created OK. Code hasn't changed but added anyway below:

    Fields not being added:

    assessment_type[].assessment_type_pk

    unit_outcomes

    <div id="assessment_form">
        <editor-field name="assessment.assessment"></editor-field>
        <editor-field name="assessment.unit_fk"></editor-field>
        <editor-field name="assessment.weighting"></editor-field>
        <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
        <editor-field name="unit_outcomes"></editor-field>
      </div>
    </div>
    <script type="text/javascript">
            var editor; // use a global for the submit and return data rendering in the examples
    
    $(document).ready(function() {
    
        var program = $('#program_field').val();
        var user = $('#user_field').val();
        var permission = $('#permission_field').val();
    
    
    
        $('#main-menu').smartmenus();
    
        $('#program_levels').change(function(e) {
            window.location.href = 'edit_' + this.value + ".php?program=" + program + '&user=' + user;
        });
    
    
        $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;
    
        var editor = new $.fn.dataTable.Editor({
            ajax: "program_data/assessment_data.php",
            table: "#assessment_table",
            "autoWidth": true,
            template: '#assessment_form',
            fields: [{
                label: "Assessment:",
                name: "assessment.assessment",
                type: "ckeditor"
            }, {
                label: "Unit:",
                name: "assessment.unit_fk",
                type: "select",
                placeholder: "Select Unit..."
            }, {
                label: "Weighting:",
                name: "assessment.weighting"
            }, {
                label: "Assessment type:",
                name: "assessment_type[].assessment_type_pk",
                type: "select",
                placeholder: 'Select an Assessment Type...',
            }, {
                label: "Unit Outcome:",
                name: "unit_outcomes",
                type: "select"
            }]
        });
    
    
        editor.dependent('unit_outcomes', function(val, data, callback) {
            editor.field('assessment.unit_fk').input().on('change', function(e, d) {
                //check if user or editor initiated update
                if (d && d.editorSet) return;
                $.ajax({
                    url: 'program_data/get_unit_outcomes.php',
                    data: {
                        "unit": editor.get('assessment.unit_fk')
                    },
                    type: 'POST',
                    dataType: 'JSON',
                    success: function(data) {
                        callback(data);
                    }
                });
            });
        });
    
        var table = $('#assessment_table').DataTable({
            responsive: true,
            ajax: "program_data/assessment_data.php",
            dom: "Blfrtip",
            columns: [{
                data: "assessment.assessment"
            }, {
                data: "unit.unit_name"
            }, {
                data: "assessment.weighting"
            }, {
                data: "assessment_type",
                render: "[, ].assessment_type"
            }, {
                data: "unit_outcome",
                render: "[, ].unit_outcome"
            }, {
                data: "assessment.modified"
            }, {
                data: "assessment.modified_by"
            }],
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            buttons: []
        });
    });
    

    and the PHP code for the dependant select 'unit_outcomes'

    $unit = $_POST['unit'];
    
    $data = array();
    
    $query = "SELECT * FROM unit, unit_outcome, unit_unit_outcome_lookup WHERE unit_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk AND unit_unit_outcome_lookup.unit_fk = unit.unit_pk AND unit.unit_pk = '$unit'";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("label"=>substr(strip_tags($row['unit_outcome']), 0, 100) . '...', "value"=>$row['unit_outcome_pk']);
    }
    
    $temp = array('unit_outcomes'=>$data);
    $json = array('options'=>$temp);
    echo json_encode($json);
    
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    Can you show me what is being submitted to the server when you click the edit (or create) button please? The browser's Network inspector will show you that on the "header" section of the Ajax request.

    Thanks,
    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Hi Allan

    {"data":[{"DT_RowId":"row_5","assessment":{"assessment":"
    
    test 6<\/p>","weighting":"10%","modified":"2020-03-11 08:59:02","modified_by":"00082563","unit_fk":"4"},"unit":{"unit_name":"Integrated Medical Practice 2"},"assessment_type":[],"unit_outcome":[]}]}
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Response Headers

    HTTP/1.1 200 OK
    Date: Wed, 11 Mar 2020 01:03:19 GMT
    Server: Apache/2.2.15 (Red Hat)
    X-Powered-By: PHP/5.3.3
    Expires: Thu, 19 Nov 1981 08:52:00 GMT
    Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
    Pragma: no-cache
    Content-Length: 258
    Connection: close
    Content-Type: text/html; charset=UTF-8

    Request Headers

    Host: healthed.hms.uwa.edu.au
    User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:73.0) Gecko/20100101 Firefox/73.0
    Accept: application/json, text/javascript, /; q=0.01
    Accept-Language: en-GB,en;q=0.5
    Accept-Encoding: gzip, deflate, br
    Content-Type: application/x-www-form-urlencoded; charset=UTF-8
    X-Requested-With: XMLHttpRequest
    Content-Length: 243
    Origin: https://healthed.hms.uwa.edu.au
    Connection: keep-alive
    Referer: https://healthed.hms.uwa.edu.au/curriculum_mapper/programs/cm_md/edit_assessment.php?program=cm_md&user=00082563
    Cookie: PHPSESSID=akv3mecsvh4kdognm9smd603s3
    Pragma: no-cache
    Cache-Control: no-cache

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Hi Allan

    I managed to fix those two fields by:

    assessment_type[].assessment_type_pk

    changed:

                label: "Assessment type:",
                name: "assessment_type[].assessment_type_pk",
                type: "select"
    

    to

                label: "Assessment type:",
                name: "assessment_type[].assessment_type_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
    

    unit_outcomes

    changed

                label: "Unit Outcome:",
                name: "unit_outcomes",
                type: "select"
    

    to

                label: "Unit Outcome:",
                name: "unit_outcome[].unit_outcome_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
    

    and accordingly changed the editor.dependant from unit outcomes to unit_outcome[].unit_outcome_pk:

        editor.dependent('unit_outcome[].unit_outcome_pk', function(val, data, callback) {
            editor.field('assessment.unit_fk').input().on('change', function(e, d) {
                //check if user or editor initiated update
                if (d && d.editorSet) return;
                $.ajax({
                    url: 'program_data/get_unit_outcomes.php',
                    data: {
                        "unit": editor.get('assessment.unit_fk')
                    },
                    type: 'POST',
                    dataType: 'JSON',
                    success: function(data) {
                        callback(data);
                    }
                });
            });
        });
    

    and of course the PHP script to get the dependant select data:

    $unit = $_POST['unit'];
    
    $data = array();
    
    $query = "SELECT * FROM unit, unit_outcome, unit_unit_outcome_lookup WHERE unit_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk AND unit_unit_outcome_lookup.unit_fk = unit.unit_pk AND unit.unit_pk = '$unit'";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("label"=>substr(strip_tags($row['unit_outcome']), 0, 100) . '...', "value"=>$row['unit_outcome_pk']);
    }
    
    $temp = array('unit_outcome[].unit_outcome_pk'=>$data);
    $json = array('options'=>$temp);
    echo json_encode($json);
    

    Full code:

      <div class='table_container'>
        <table id='assessment_table' class='display' style="width:100%">
          <thead>
            <tr>
              <th>Assessment</th>
              <th>Unit</th>
              <th>Weighting</th>
              <th>Assessment Type</th>
              <th>Unit Outcome</th>
              <th>Modified</th>
              <th>Modified By</th>
            </tr>
          </thead>
          <tbody>
          </tbody>
        </table>
      </div>
      <div id="assessment_form">
        <editor-field name="assessment.assessment"></editor-field>
        <editor-field name="assessment.unit_fk"></editor-field>
        <editor-field name="assessment.weighting"></editor-field>
        <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
        <editor-field name="unit_outcome[].unit_outcome_pk"></editor-field>
      </div>
    </div>
    <script type="text/javascript">
    var editor; // use a global for the submit and return data rendering in the examples
    
    $(document).ready(function() {
    
        $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;
    
        var editor = new $.fn.dataTable.Editor({
            ajax: "program_data/assessment_data.php",
            table: "#assessment_table",
            "autoWidth": true,
            template: '#assessment_form',
            fields: [{
                label: "Assessment:",
                name: "assessment.assessment",
                type: "ckeditor"
            }, {
                label: "Unit:",
                name: "assessment.unit_fk",
                type: "select",
                placeholder: "Select Unit..."
            }, {
                label: "Weighting:",
                name: "assessment.weighting"
            }, {
                label: "Assessment type:",
                name: "assessment_type[].assessment_type_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
            }, {
                label: "Unit Outcome:",
                name: "unit_outcome[].unit_outcome_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
            }]
        });
    
    
        editor.dependent('unit_outcome[].unit_outcome_pk', function(val, data, callback) {
            editor.field('assessment.unit_fk').input().on('change', function(e, d) {
                //check if user or editor initiated update
                if (d && d.editorSet) return;
                $.ajax({
                    url: 'program_data/get_unit_outcomes.php',
                    data: {
                        "unit": editor.get('assessment.unit_fk')
                    },
                    type: 'POST',
                    dataType: 'JSON',
                    success: function(data) {
                        callback(data);
                    }
                });
            });
        });
    
        var table = $('#assessment_table').DataTable({
            responsive: true,
            ajax: "program_data/assessment_data.php",
            dom: "Blfrtip",
            columns: [{
                data: "assessment.assessment"
            }, {
                data: "unit.unit_name"
            }, {
                data: "assessment.weighting"
            }, {
                data: "assessment_type",
                render: "[, ].assessment_type"
            }, {
                data: "unit_outcome",
                render: "[, ].unit_outcome"
            }, {
                data: "assessment.modified"
            }, {
                data: "assessment.modified_by"
            }],
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            buttons: []
        });
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Could you explain why this doesn't work for me?

    label: "Assessment type:",
    name: "assessment_type[].assessment_type_pk",
    type: "select"
    

    but this works:

    label: "Assessment type:",
    name: "assessment_type[].assessment_type_pk",
    type: "select",
    placeholder: 'No selection',
    placeholderDisabled: false,
    placeholderValue: 0,
    multiple: true
    
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    Answer ✓

    name: "assessment_type[].assessment_type_pk",

    Is telling it to expect an array. So multiple has to be true (since otherwise it expects a scalar).

    If you are working with an Mjoin then you are always working with arrays (Mjoin is one-to-many after all).

    If you are using a leftJoin (one-to-one) then you would not use multiple: true.

    Does that make a bit more sense now?

    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Thanks Allan, that's clear.

This discussion has been closed.