Dynamic drop-down list in editor form

Dynamic drop-down list in editor form

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
edited February 2021 in Editor

I would like to replace the hard coded options in the code below with a dynamic data from MySQL DB through PHP. Is there clear way of doing this. An example would be good.

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/discipline_outcome_data.php",
                table: "#discipline_outcome_table",
                template: '#discipline_outcome_form',
                fields: [  {
                    label: "Discipline:",
                    name: "discipline_outcome.discipline_fk",
                    type: "select",
                    placeholder: 'Choose discipline...',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    options: [
                        { label: 'Emergency Medicine', value: 1 },
                        { label: 'General Practice', value: 2 },
                        { label: 'Internal Medicine', value: 3 },
                        { label: 'Obstretics & Gynaecology', value: 4 },
                        { label: 'Paediatrics', value: 5 },
                        { label: 'Psychiatry', value: 6 },
                        { label: 'Surgery', value: 7 }      
                    ]
                },...

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited February 2021

    I have tried:

    var discipline_options = [];
     
     $.getJSON('get_disciplines.php', function (data) {
         $.each(data, function (index) {
             discipline_options.push({
                 value: data[index].value,
                 label: data[index].text
             });
         });
     editor.field( 'discipline_outcome.discipline_fk' ).update();
     });
    
                var editor = new $.fn.dataTable.Editor( {
                    ajax: "program_data/discipline_outcome_data.php",
                    table: "#discipline_outcome_table",
                    template: '#discipline_outcome_form',
                    fields: [  {
                        label: "Discipline:",
                        name: "discipline_outcome.discipline_fk",
                        type: "select",
                        placeholder: 'Choose discipline...',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        options: []
                    },...
    

    with get_disciplines.php:

    $data = array();
    
    $query = "SELECT * FROM discipline";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("label"=>$row['discipline'], "value"=>$row['discipline_pk']);
    }
    
    $temp = array('disciplines[].discipline_pk'=>$data);
    $json = array('options'=>$temp);
    echo json_encode($json);
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    That possibly should be:

    editor.field( 'discipline_outcome.discipline_fk' ).update(discipline_options);
    

    but not working still...

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    The PHP script get_disciplines.php is returning:

    {"options":{"disciplines[].discipline_pk":[{"label":"Emergency Medicine","value":"1"},{"label":"General Practice","value":"2"},{"label":"Internal Medicine","value":"3"},{"label":"Obstetrics & Gynaecology","value":"4"},{"label":"Paediatrics & Childcare","value":"5"},{"label":"Psychiatry","value":"6"},{"label":"Surgery","value":"7"},{"label":"Infection & Immunity","value":"8"},{"label":"Anaesthesia & Pain Medicine","value":"10"},{"label":"Ophthalmology","value":"11"},{"label":"Geriatric Medicine","value":"12"},{"label":"Rheumatology","value":"13"},{"label":"Palliative Care","value":"14"},{"label":"Oncology","value":"15"},{"label":"Anatomy","value":"16"},{"label":"Aboriginal Health","value":"17"},{"label":"Behavioural Science","value":"18"},{"label":"Clinical Skills","value":"19"},{"label":"Health Humanities","value":"20"},{"label":"Immunology","value":"21"},{"label":"Microbiology","value":"22"},{"label":"Pathology","value":"23"},{"label":"Pharmacology","value":"24"},{"label":"Physiology","value":"25"},{"label":"Population Health","value":"26"}]}}
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Is this for a regular left join? This example shows how to do it, with the docs here.

    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Hi Allan

    The select list discipline_outcome.discipline_fk is actually just a list of disciplines that have no relationship with the the other data that is being served to the form fields. The name of the select list is probably misleading so I have changed it to:

    var editor = new $.fn.dataTable.Editor( {
                    ajax: "program_data/discipline_outcome_data.php",
                    table: "#discipline_outcome_table",
                    template: '#discipline_outcome_form',
                    fields: [  {
                        label: "Discipline:",
                        name: "discipline.discipline_pk",
                        type: "select",
                        placeholder: 'Choose discipline...',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        options: []
                    },
    

    So what I am tying to do is just populate that select list with a separate function, bu it's not working. The JSON may be wrong from the PHP script or the method may be wrong to populate the list, I'm not sure:

    var discipline_options = [];
     
    $.getJSON('program_data/get_disciplines.php', {
        },
        function(data) {
            var option = {};
            $.each(data, function(i, e) {
                option.label = e.text;
                option.value = e.id;
                discipline_options.push(option);
                option = {};
            });
        }
    ).done(function() {
        editor.field('discipline.discipline_pk' ).update(discipline_options);
    });
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited February 2021

    The JSON from get_displines.php is like:

            {
          "options": {
            "disciplines[].discipline_pk": [
              {
                "label": "Emergency Medicine",
                "value": "1"
              },
              {
                "label": "General Practice",
                "value": "2"
              },
              {
                "label": "Internal Medicine",
                "value": "3"
              }
            ]
          }
        }
    

    and get_disciplines.php:

    $data = array();
    
    $query = "SELECT * FROM discipline";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("label"=>$row['discipline'], "value"=>$row['discipline_pk']);
    }
    
    $temp = array('disciplines[].discipline_pk'=>$data);
    $json = array('options'=>$temp);
    echo json_encode($json);
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    I got it working using:

    var discipline_options = [];
                
    $.getJSON("program_data/get_disciplines.php", function(data) {
            var option = {};
            $.each(data, function(i,e) {
                option.label = e.text;
                option.value = e.id;
                discipline_options.push(option);
                option = {};
            });
        }
    ).done(function() {
        editor.field('discipline.discipline_pk').update(discipline_options);
    });
    

    and in the editor:

    var editor = new $.fn.dataTable.Editor( {
                    ajax: "program_data/discipline_outcome_data.php",
                    table: "#discipline_outcome_table",
                    template: '#discipline_outcome_form',
                    fields: [  {
                        label: "Discipline:",
                        name: "discipline.discipline_pk",
                        type: "select",
                        placeholder: 'Choose discipline...',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        options: []
                    },
    

    and the get_disciplines.php:

    $data = array();
    
    $query = "SELECT * FROM discipline";
    $result = $connection->query( $query );
    
    while ($row = mysqli_fetch_array($result)) {
        $data[] = array("text"=>$row['discipline'], "id"=>$row['discipline_pk']);
    }
    
    echo json_encode($data);
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Excellent, thanks for reporting back,

    Colin

This discussion has been closed.