How to populate select field with data from another table?

How to populate select field with data from another table?

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

I have been reading https://datatables.net/forums/discussion/comment/137978/#Comment_137978 and https://datatables.net/forums/discussion/53705 but I'm just not getting it. Does anyone have a more involved example? Is there another way to achieve this such as using ajax?

I have a dept_code (INT) and a department name (VARCHAR) in a table. I would like to populate a select field in editor to populate only certain departments based on dept_code. The Datatable data itself is in a different table.

My editor code looks like this:

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'ajax/csirs-departments_test.php',
...
{
                label: 'Department:',
                name: 'csirs.department',
                type: 'select'
                }
...

           }
        ]
    } );

    $.getJSON( 'ajax/csirs_departments_test.php', function( json ) {
      editor.field( 'csirs.department' ).update( json.options );
    } );

    $('#csirs').DataTable( {
...

I can see in the developer tools that I am getting just the departments I chose by dept_code but the select shows all departments and not just the one I chose. I can't figure out why.

This question has an accepted answers - jump to answer

Answers

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited November 2020

    Some more information, I have a lk_departments table that has local, county, state and federal departments defined and each one has a unique dept_code. For a particular Editor form I only want to show the state departments which is a list of about 230 departments. The whole list totals to around 1,020 and I want to cut that down to only the departments for the state.

    I'm pretty sure I can achieve what I need by using the options in my php script.

    Field::inst( 'csirs.department' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Department is required.' ) ) )
                        ->options( Options::inst()
                        ->table( 'lk_departments' )
                        ->value( 'dept_code' )
                        ->label( 'department' )
                        ->where( 'dept_code', 16, '=' )
                        ),
            Field::inst( 'lk_departments.department' ),
    
    ... more fields
    
    ->leftJoin( 'lk_departments', 'lk_departments.dept_code', '=', 'csirs.department' )
    ... post, etc.
    
    

    However the departments are returning null. I've checked the fields in the lk_departments table and dept_code exists and is an INT. So once again I'm stuck, Arggggg!

    The sql query looks like this in the developer tools;

    SELECT DISTINCT  `dept_code` as 'dept_code', `department` as 'department' FROM  `lk_departments` WHERE `dept_code` IS NULL
    

    Why is dept_code being set to NULL? I have php error reporting turned on and ->debug(true) and I see no errors.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Can you give me a link to the page so I can investigate please?

    Also, if you are Ajax loading data for your DataTable using (ajax) then your $.getJSON in the first block shown above won't be needed (but the code isn't complete so I can't say for sure).

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited November 2020

    Yes the first block is using getJSON. While I did some reading trying to get more information on getJSON I found some code that uses the ->where condition and that looks like that would work for what I need to do.
    I am replacing the first block of code that uses getJSON with the ->options in my PHP script as in the second block. I only need the states and this looks to be a much simpler way to achieve that for both Editor and the Datatable.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    ->where will work just fine from the table's own ajax option.

    Assuming that the DataTable also calls ajax/csirs-departments_test.php using its ajax option, what does it return please?

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited November 2020

    It calls on csirs-con.db and has this code and the Field is csirs.department...

    <?php
     // Enable error reporting for debugging
    error_reporting(E_ALL);
    ini_set('log_errors', '1');
    ini_set('display_errors', '1'); // display errors in browser. comment out for production.
    
    // DataTables PHP library
    include( "common/php/DataTables.php" );
     
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
     
    Editor::inst( $db, 'csirs', 'id' )
        ->field(
            Field::inst( 'csirs.id' ),
    
            Field::inst( 'csirs.csirs_number' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'CSIRS number is required.' ) ) )
                ->validator( Validate::numeric('.', ValidateOptions::inst()
                    ->message( 'Error - Numeric only accepted.' ) ) )
                ->validator( Validate::minLen('6', ValidateOptions::inst()
                    ->message( 'CSIRS number should be 6 numbers.' ) ) )
                    ->validator( Validate::maxLen('6', ValidateOptions::inst()
                    ->message( 'CSIRS number should be 6 numbers.' ) ) )
                ->validator( Validate::unique( ValidateOptions::inst()
                    ->message( 'This CSIRS number has already been entered in the database.' ) ) ),
    
            Field::inst( 'csirs.date' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Date is required.' ) ) ),
    
            Field::inst( 'csirs.department' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Department is required.' ) ) )
                        ->options( Options::inst()
                        ->table( 'lk_departments' )
                        ->value( 'dept_code' )
                        ->label( 'department' )
                        ->where( 'dept_code', 16, '=' )
                        ),
            Field::inst( 'lk_departments.department' ),
    
            Field::inst( 'csirs.primaryType' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Primary type is required.' ) ) )
                        ->options( Options::inst()
                        ->table( 'lk_csirs_primaryTypes' )
                        ->value( 'id' )
                        ->label( 'primaryType' )
                        ),
            Field::inst( 'lk_csirs_primaryTypes.primaryType' ),
                    
            Field::inst( 'csirs.notes' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Notes are required.' ) ) )
        )
    
            ->leftJoin( 'lk_departments', 'lk_departments.dept_code', '=', 'csirs.department' )
            ->leftJoin( 'lk_csirs_primaryTypes', 'lk_csirs_primaryTypes.id', '=', 'csirs.primaryType' )
    
        ->debug ( true ) // only set true for development
        ->process($_POST)
        ->json();
    

    The returned data that I see in the developer tools is;

    {data: [{DT_RowId: "row_6",…}, {DT_RowId: "row_7",…}, {DT_RowId: "row_8",…}],…}
    data: [{DT_RowId: "row_6",…}, {DT_RowId: "row_7",…}, {DT_RowId: "row_8",…}]
    0: {DT_RowId: "row_6",…}
    1: {DT_RowId: "row_7",…}
    2: {DT_RowId: "row_8",…}
    debug: [{,…}, {,…}, {,…}]
    files: []
    options: {csirs.department: [],…}
    csirs.department: []
    csirs.primaryType: [{label: "DOS", value: "1"}, {label: "Information Asset Property Loss", value: "2"},…]
    

    Are you able to log in?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Hi,

    Many thanks for the PM with the login details - sorry for the delay getting back to you - I completely missed the PM!

    So taking the csirs.department field, you have ->where( 'dept_code', 16, '=' ) in the PHP. And the return from the server for that field is csirs.department: [] - which suggests to me that there are no items in the database which have dept_code = 16 on that table.

    Is that not what you are expecting here?

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    No problem Allen. I was out of town a few days anyway. Sneaking in some work when the wife wasn't looking or was asleep :wink:

    Yes I am not expecting null. There are 230 items/departments with a dept_code of 16 in the table lk_departments when I query the database from the command line.

    Perhaps this would help along with the PHP script I'm using above declaring the tables and fields and joins...

    describe lk_departments; // secondary table that links dept_code to department names
    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra          |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | dept_code  | int(10) unsigned | YES  | MUL | NULL    |                |
    | department | varchar(150)     | YES  |     | NULL    |                |
    +------------+------------------+------+-----+---------+----------------+
    
    describe csirs; // the main table for this datatable
    +--------------+------------------+------+-----+---------+----------------+
    | Field        | Type             | Null | Key | Default | Extra          |
    +--------------+------------------+------+-----+---------+----------------+
    | id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | csirs_number | varchar(6)       | YES  |     | NULL    |                |
    | date         | date             | YES  |     | NULL    |                |
    | department   | int(10) unsigned | NO   | MUL | NULL    |                |
    | primaryType  | int(10) unsigned | NO   | MUL | NULL    |                |
    | notes        | varchar(255)     | YES  |     | NULL    |                |
    +--------------+------------------+------+-----+---------+----------------+
    
    

    Do I need a dept_code field in the csirs table? If so they are all going to be 16.
    In the Options::inst(), is this calling on the lk_departments table for dept_code or is it expecting dept_code to be in the csirs table?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Looking at the SQL debug (since you have the debug() option enabled in the PHP it is showing that the following is being executed:

    SELECT DISTINCT `id` as 'id', `department` as 'department' FROM `lk_departments` WHERE `dept_code` IS NULL 
    

    That explains the 0 results!

    I had to refer to the docs myself to understand why that wasn't working. Options->where() expects a function, not a condition (which the Editor->where() does allow).

    The second example just below that link point shows how it can be done. In this case:

            ->where( function ($q) {
                $q->where( 'dept_code', 16 );
            }
    

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    That did the trick Allen. Many thanks again. I was reading the docs for the Where condition. I would have never thought about the Join docs.

This discussion has been closed.