Dependant selects

peterbrowne · March 2020

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 ;

peterbrowne · March 2020

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

peterbrowne · 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
    ]
] );

peterbrowne · 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

peterbrowne · 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>"}]}}

allan · March 2020

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

peterbrowne · March 2020

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

peterbrowne · March 2020

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.

peterbrowne · March 2020

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:

peterbrowne · March 2020

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']);

peterbrowne · March 2020

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.

peterbrowne · 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!

peterbrowne · 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.

peterbrowne · 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":[]}]}

peterbrowne · 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);

allan · March 2020

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

peterbrowne · March 2020

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":[]}]}

peterbrowne · March 2020

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

peterbrowne · March 2020

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: []
    });

peterbrowne · March 2020

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

allan · March 2020

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

peterbrowne · March 2020

Thanks Allan, that's clear.

Dependant selects

Dependant selects

Answers

Howdy, Stranger!

Categories

DataTables

Dependant selects

Dependant selects

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables