Dynamic drop-down list in editor form

peterbrowne

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 }      
                    ]
                },...

peterbrowne

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

peterbrowne

That possibly should be:

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

but not working still...

peterbrowne

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

allan

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

Allan

peterbrowne

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

peterbrowne

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

peterbrowne

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

colin

Excellent, thanks for reporting back,

Colin