Populate select from unrelated table

Populate select from unrelated table

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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);
                                  });
    
    ...
    
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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);
    
  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin
    Answer ✓

    Nice one - thanks for posting back with your solution.

    Allan

Sign In or Register to comment.