How to populate select field with data from another table?

th3t1ck

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.

th3t1ck

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.

allan

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

th3t1ck

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.

allan

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

th3t1ck

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?

allan

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

th3t1ck

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

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?

allan

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

th3t1ck

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.