Editor Select Input Values From Ajax

th3t1ck

I would like to populate the options in an editor select list to reflect only users who are active. The select value defaults to the logged in user but can be changed. How can I accomplish this?
I am querying tableA which contains case details and the user who created the case. tableB which contains user information such as username and user_id and id (primary key) and tableC which is a lookup table containing "Active/Inactive". I have tried the following...

var active_users = [];
$(document).ready(function() {
             
    $.getJSON('active_users.php', function(data) {
            var option = {};
            $.each(data, function(i,e) {
                option.label = e.text;
                option.value = e.id;
                active_users.push(option);
                option = {};
            });
        }
    ).done(function() {
        editor.field('cases.user_id').update(active_users);
    });

    var editor = new $.fn.dataTable.Editor( {
        ajax: 'cases_test-con.php',
        table: '#cases',
        template: '#customForm',
        fields: [ {
{
                label: 'User ID:',
                type: 'select',
                name: 'cases.user_id',
                options: active_users,
                def: "; echo json_encode( $_SESSION['id'], JSON_NUMERIC_CHECK ) ;
print"
            }
    etc. ...

I get no errors and the page loads fine but the editor select values contain all users and not just the "Active" users. The return data from the ajax script looks like this which does contain only the Active users...

[
{"label":"A16479","value":"3"},{"label":"014668","value":"15"},{"label":"014670","value":"16"},{"label":"018375","value":"17"},{"label":"017168","value":"18"},{"label":"018698","value":"19"},{"label":"A17007","value":"24"},{"label":"A18201","value":"25"},{"label":"013698","value":"29"},{"label":"019168","value":"33"},{"label":"019852","value":"34"},{"label":"019944","value":"35"},{"label":"020155","value":"36"},{"label":"A00001","value":"46"},{"label":"A00002","value":"47"},{"label":"017369","value":"48"},{"label":"A18520","value":"49"}
]

I also tired the following with the same result as above...

Editor::inst( $db, 'cases', 'case_number' )
    ->field( ...
        Field::inst( 'cases.user_id' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'ID is required.' ) ) )
                    ->options( Options::inst()
                    ->table( 'users' )
                    ->value( 'id' )
                    ->label( 'user_id' )
                    ),
        Field::inst( 'tbl_invest_id.user_id' ),
etc. ...

        ->leftJoin( 'users AS tbl_invest_id', 'tbl_invest_id.id', '=', 'cases.user_id' )
        ->leftJoin( 'users AS tbl_invest_status', 'tbl_invest_status.id', '=', 'cases.user_id' )


->where( function ( $q ) use ( $year ) {
        $q->where( 'start_date', $year.'-%', 'LIKE');
        $q->and_where( 'tbl_invest_status.status', '(SELECT id FROM user_status WHERE status = "Active")', 'IN', false );
    } )
    ->process( $_POST )
    ->json();

Can I put a Where clause in the ->options instead of the location I am trying to use "->where( function ( $q ) use ( $year ) { ..."? I beleive I could do this with the correct SQL statement. Any help is appreciated.

allan

Can I put a Where clause in the ->options instead of the location

Yes indeed you can, and that would be the way to do what you are looking for.

I don't actually see the Options PHP code in the above (presumably it was in the etc part, but you'd do something like:

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'users' )
        ->value( 'id' )
        ->label( 'name' )
        ->where( function ($q) {
            $q->where( 'active', true );
        }
    );

Assuming you are loading the JSON through DataTables, you can thus also drop the $.getJSON('active_u... block since Editor can make use of the DataTables Ajax loaded JSON.

Allan

th3t1ck

Thank you Allan. I'll look that link over. My active_users.php file looks like this.
```
<?php
// Enable error reporting for debugging. Comment out for production.
error_reporting(E_ALL);
ini_set('log_errors', '1'); // log errors
ini_set('display_errors', '1'); // display errors in browser.

include( '../db_connect.php' );

$data = array();

$users = "SELECT user_id AS value, id AS label
FROM users
WHERE status=1";

$result = mysqli_query( $dbConn, $users );

if( !$result ) {
print "

Error: No result returned." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno( $dbConn ) . PHP_EOL;
    print"

\n";
exit;
}

while ($row = mysqli_fetch_array($result)) {
$data[] = array("label"=>$row[0], "value"=>$row[1]);
}

echo json_encode($data);

<?php > ``` ?>

th3t1ck

That worked just the way I wanted. Thank you Allan.