Populate select from unrelated table

peterbrowne

I want to populate a select from a DB table and column unit.unit_name that is unrelated to other data on the editor form and associated data. I'm not sure how to do that.

I have tried the following but expectedly get

DataTables warning: table id=learning_event_outcome_table - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'unit' in 'field list'

Editor::inst( $db_cm_mjd_plmed, 'learning_event_outcome', 'learning_event_outcome_pk' )    
    ->field(
        Field::inst( 'learning_event_outcome.learning_event_outcome' ),
        Field::inst( 'learning_event_outcome.modified' ),
        Field::inst( 'learning_event_outcome.modified_by' )->setValue( $user ),
    Field::inst( 'unit' )
    ->options(Options::inst()
        ->table('unit')
        ->value('unit_name')
        ->label('unit')
        ->order('unit_name asc')
        ->render( function ($row) {
            return $row['unit_name'];
        } )
    )
    )

peterbrowne

OK, got it working with:

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/learning_event_outcome_data.php",
                table: "#learning_event_outcome_table",
                template: '#learning_event_outcome_form',
                fields: [ {
                    label: "Learning Event Outcome:",
                    name: "learning_event_outcome.learning_event_outcome",
                    type: "ckeditor"
                }, {
                    label: "unit:",
                    name: "unit.unit_pk",
                    type: "select",
                    placeholder: "Select a Unit..."
                }, {
                    label: "Learning Events:",
                    name: "learning_event[].learning_event_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Unit Outcomes:",
                    name: "unit_outcome[].unit_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );
                        
                        
            var units = [];
             
            $.getJSON("program_data/get_units.php", function(data) {
                var option = {};
                $.each(data, function(i,e) {
                    option.label = e.text;
                    option.value = e.id;
                    units.push(option);
                    option = {};
                });
            }).done(function(){editor.field('unit.unit_pk').update(units);
                              });

...

peterbrowne

and the get_units.php script...

$data = array();

$query = "SELECT * FROM unit";
$result = $connection->query( $query );

while ($row = mysqli_fetch_array($result)) {
    $data[] = array("text"=>$row['unit_code'], "id"=>$row['unit_pk']);
}

echo json_encode($data);

allan

Nice one - thanks for posting back with your solution.

Allan