I want to populate a select field's options dynamically from a MYSQL DB

Alchetec

I want to populate the PermissionToEnter.unit field dynamically with a list of options
from a MYSQL DB. Here are the tables:

PermissionToEnter

+----+------+----------------+------------+------------+----------+
| id | unit | name           |  from      |   to       | issuekey |
+----+------+----------------+------------+------------+----------+
|  1 | 4201 | Peter Piper    | 2016-03-01 | 2016-03-15 |     1    |
|  2 | 4201 | Carol Smith    | 2016-01-25 | 2016-02-25 |     0    |
|  3 | 4211 | John Doe       | 2016-02-01 | 2016-02-29 |     0    |
|  4 | 4201 | Tom Thumb      | 2016-02-20 | 2016-02-20 |     1    |
|  5 | 4201 | John Doe       | 2016-02-16 | 2016-02-29 |     0    |
|  6 | 4211 | Sally Seashore | 2016-02-17 | 2016-03-17 |     1    |
|  7 | 4203 | Guy Worker     | 2016-03-01 | 2016-03-15 |     0    |
+----+------+----------------+------------+------------+----------+

People

+----+------+---------------+---------+----------+
| id | unit | name          | ownerid | username |
+----+------+---------------+---------+----------+
|  1 | 4201 | David Jones   |    1    | djones   |
|  2 | 4201 | William Smith |    2    | wsmith   |
|  3 | 4211 | Paul Doe      |    3    | pdoe     |
|  4 | 4210 | Joe Renter    |    3    |          |
|  5 | 4204 | Bob Owner     |    5    | bowner   |
|  6 | 4203 | John Renter   |    5    | jrenter  |
|  7 | 4205 | Sally Renter  |    5    | srenter  |
+----+------+---------------+---------+----------+

Valid units for PermissionToEnter is defined by the sql (the username is provided within the php code):

set @id = (select id from People where username = 'username');
(SELECT unit FROM People WHERE id = @id)
UNION DISTINCT
(SELECT Unit from People WHERE ownerid IN (select ownerid from People where ownerid = @id))
ORDER BY unit;

So the given 'username' would get the following results:

• djones - 4201
• wsmith - 4201
• pdoe - 4211, 4210
• bowner - 4204, 4203, 4205
• jrenter - 4203
• srenter - 4205

PermissionToEnter.php

<?php

// DataTables PHP library and database connection
include( "../../Editor/php/DataTables.php" );

$unit = '4201'  // This is for illustration purpose. In reallity, it is set via a MYSQL DB read 

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'PermissionToEnter' )
    ->fields(
        Field::inst( 'PermissionToEnter.unit' ),
        Field::inst( 'PermissionToEnter.name' ),
        Field::inst( 'PermissionToEnter.from' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'m-d-Y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'm-d-Y' )
            ->setFormatter( 'Format::date_format_to_sql', 'm-d-Y' ),
        Field::inst( 'PermissionToEnter.to' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'m-d-Y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'm-d-Y' )
            ->setFormatter( 'Format::date_format_to_sql', 'm-d-Y' ),
        Field::inst( 'PermissionToEnter.issuekey' )
            ->setFormatter( function ( $val, $data, $opts ) {
                        return ! $val ? 0 : 1;
                    } )
    )
    ->Where( function ( $q ) use ($unit) {
        $q
        ->where( 'PermissionToEnter.unit', $unit, "LIKE" )
        ->and_where( 'PermissionToEnter.to', date('Y-m-d'), ">=" );
    } )
    ->process( $_POST )
    ->json();

PermissionToEnter.js

/*
 * Editor client script for DB table PermissionToEnter
 */
(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: document.location.origin+'/Nexus/php/PermissionToEnter.php',
        table: '#PermissionToEnter',
        fields: [
            {
                "label": "Unit",
                "name": "PermissionToEnter.unit",
                "type": "select"
            },
            {
                "label": "Name",
                "name": "PermissionToEnter.name",
            },
            {
                "label": "From",
                "name": "PermissionToEnter.from",
                "type": "datetime",
                "format": "MM-DD-YYYY",
                def: function () { return new Date() },
                "opts": {
                    "minDate": new Date()
                }
            },
            {
                "label": "To",
                "name": "PermissionToEnter.to",
                "type": "datetime",
                "format": "MM-DD-YYYY"
            },
            {
                "label": "IssueKey",
                "name": "PermissionToEnter.issuekey",
                "type": "checkbox",
                "separator": ",",
                "options": [
                    { "label": '', "value": 1 }
                ]
            }
        ]
    } );

    var table = $('#PermissionToEnter').DataTable( {
        dom: 'Bfrtip',
        ajax: document.location.origin+'/Nexus/php/PermissionToEnter.php',
        orderFixed: [ 0, 'asc' ],
        columns: [
            {
                "data": "PermissionToEnter.unit",
                "visible": false,
                "searchable": true
            },
            {
                "data": "PermissionToEnter.name"
            },
            {
                "data": "PermissionToEnter.from"
            },
            {
                "data": "PermissionToEnter.to"
            },
            {
                "data": "PermissionToEnter.issuekey",
                render: function ( data, type, row ) {
                                if ( type === 'display' ) {
                                    return '<input type="checkbox" class="editor-issuekey">';
                                }
                                return data;
                        },
                        className: "dt-body-center"
            }
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ],
        rowCallback: function ( row, data ) {
        // Set the checked state of the checkbox in the table
            $('input.editor-issuekey', row).prop( 'checked', data.PermissionToEnter.issuekey == 1 );
        },
        drawCallback: function ( settings ) {
            var api = this.api();
            var rows = api.rows( {page:'current'} ).nodes();
            var last=null;

            api.column(0, {page:'current'} ).data().each( function ( group, i ) {
                if ( last !== group ) {
                    $(rows).eq( i ).before(
                        '<tr class="group"><td colspan="4">'+group+'</td></tr>'
                    );
                    last = group;
                }
            } );
        }
    } );
    
    $('#PermissionToEnter').on( 'click', 'input:checkbox', function () {
        event.preventDefault();
    } );
} );
}(jQuery));

PermissionToEnter.html

<!doctype html>
<html>
.
.
.
            <table cellpadding="0" cellspacing="0" border="0" class="cell-border" id="PermissionToEnter" width="100%">
                <thead>
                            <tr>
                                    <th>Unit</th>
                                    <th>Name</th>
                                    <th>From</th>
                                    <th>To</th>
                                    <th>Issue Key</th>
                            </tr>
                </thead>
            </table>
        </div>
    </body>
</html>

I have tried various methods I found in the forums without success. The above code doesn't have any of those attempts
as part of it. I tried doing a call to a function that would return json of the valid units but again I couldn't
get it to work. I would appreciate any help or direction to a detailed example.

As a side note, it would be nice if we could search the Datatables/Editor site without having any of the
legacy information in the results. It would make things MUCH easier for those of us that are new and find
the legacy information more noise than help.

Thanks!

allan

To confirm my understanding - as different items are selected to be edited, the options in the select list need to change? If so then the dependent() method is the way to do. Use that to make an Ajax call to the server to get the list of options (there would need to be a script on the server that accepts those requests of course).

As a side note, it would be nice if we could search the Datatables/Editor site without having any of the legacy information in the results

Agreed! The current search is terrible! Its something I'm looking into.

Allan

Alchetec

I did try the dependent(). Would you point me to or give me a detailed example of a php script that does it and the calling dependent() function, I would appreciate it VERY much.

Here is a version of what I tried:

GetUnits.php

<?php
define('WP_USE_THEMES', false);
require( '../../wp-blog-header.php' );

global $current_user;
wp_get_current_user();

echo  $current_user->user_login;
if ($current_user->ID == 0) {
    $response['status'] = array(
        'type' => 'error',
        'value' => 'Unauthorized',
    );
    $encoded = json_encode($response);
    exit($encoded);
}

// DataTables PHP library and database connection
include( "../../Editor/php/DataTables.php" );

try {
    $pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $query = $pdo->prepare("SELECT Unit FROM People WHERE WP_username = ?");
    $result = $query->execute(array($user->user_login));
    $units = array();
    while($row = $query->fetch()) {
        array_push( $units, label->$row['Unit'], value->$row['Unit'] );
    }
}
catch(PDOException $e) {
    die( "Error:" . $e->getMessage());
}

echo json_encode($units);

    editor.dependent( 'PermissionToEnter.unit', function (val, data, callback) {
        var test= new Array({"label" : "a", "value" : "a"});
        $.ajax({
            url: document.location.origin+'/Nexus/php/GetUnits.php',
            dataType: 'json',
            success: function (json) {
                for(var a=0;a < json.length;a++){
                    obj= { "label" : json[a][0], "value" : json[a][1]};
                    test.push(obj);
                }
                callback(test);
            }
        });
    });

This is something like what I had. If you give me some direction, I can try to get it working closer.

Also, how should I return an error to datatables. The above doesn't cause problems, but it also doesn't pass the error up the chain.

Again thanks!

allan

There currently isn't actually a way to propagate an error from a dependent() call to any other part of Editor or DataTables. However, since you are making your own Ajax call, you can check for the error state there and perform whatever error notification is required.

Regarding the issue you are seeing, if you use the following what do you get on the browser's console:

editor.dependent( 'PermissionToEnter.unit', function (val, data, callback) {
    var test= new Array({"label" : "a", "value" : "a"});
    $.ajax({
        url: document.location.origin+'/Nexus/php/GetUnits.php',
        dataType: 'json',
        success: function (json) {
            console.log( 1, JSON.stringify( json ) );
            for(var a=0;a < json.length;a++){
                obj= { "label" : json[a][0], "value" : json[a][1]};
                test.push(obj);
            }
            console.log( 2, JSON.stringify( json ) );
            callback(test);
        }
    });
});

It looks like the structure being passed back isn't in the format expects - you would need an options object and the field name to update:

{
    "options": {
        "position": [
            "Director",
            "Senior VP",
            "VP"
        ]
    }
}

Allan

Alchetec

The Uinit dropdown on both edit and new are the options from the initialization. The json returned from GetUntis.php is:

{"options":{"Units":[{"label":"4201","value":"4201"}]}}

Here is my current GetUntis.php code:

<?php
define('WP_USE_THEMES', false);
require( '../../wp-blog-header.php' );

global $current_user;
wp_get_current_user();

include( "../../Editor/php/DataTables.php" );
if ($current_user->ID > 0) {
    $units = array();
    try {
        $pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $query = $pdo->prepare("SELECT Unit FROM People WHERE WP_username = ?");
        $result = $query->execute(array($current_user->user_login));
        while($row = $query->fetch()) {
            array_push( $units, array('label'=>$row['Unit'], 'value'=>$row['Unit']) );
        }
    } catch(PDOException $e) {
        die( "Error:" . $e->getMessage());
    }
    $temp = array('Units'=>$units);
    $json = array('options'=>$temp);
    echo json_encode($json);
}
?>

and the current editor.dependent .js

    editor.dependent( 'PermissionToEnter.unit', function (val, data, callback) {
        var test= new Array({"label" : "a", "value" : "a"});
        $.ajax({
            url: document.location.origin+'/Nexus/php/GetUnits.php',
            dataType: 'json',
            success: function (json) {
                callback(json);
            }
        });
    });

Finally I read somewhere for it to work you need to have the field 'primed' so in the Datatables php which defines the fields I have:

    Field::inst( 'PermissionToEnter.unit' )
        ->options( 'People', 'Unit', 'People.Unit' ),

Thanks!

allan

{"options":{"Units":[{"label":"4201","value":"4201"}]}}

I assume it is the PermissionToEnter.unit select list that you want to update? In which case the returned JSON should be:

{
    "options": {
        "PermissionToEnter.unit": [{
            "label": "4201",
            "value": "4201"
        }]
    }
}

i.e. use the field name. Note that it is case sensitive as well.

Allan

Alchetec

:) Thank you!!

julioc

Good afternoon,

I´m using DataTables editor and i´m doing as the example above. I have a select list and i´m trying to load some labels and values from the database. Everything is working fine, except when i click in some line and click at the create (new) button, it shows me the form with the labels and values, but the ajax does not stop from doing calls to the server.
In my understand it should be just once.

{
    label: "Supervisor:",
    name: "Supervisor",
    fieldInfo: "Selecione o supervisor.",
    placeholder: "Selecione o supervisor",
    type: "select"
}

editor.dependent( 'Supervisor', function (val, data, callback) {
    //var test = new Array({"label" : "a", "value" : "a"});
    $.ajax({
        type: 'POST',
        url: '../asp/pesquisaSupervisor.asp',
        dataType: 'json',
        success: function (json) {
            callback(json);
        }
    });
}); 

In the console, at the time of the debug, i can see many calls to the server of this file ../asp/pesquisaSupervisor.asp

Am i doing something wrong?

allan

Can you link to the page showing the issue please? I don't immediately see anything wrong with the above code I'm afraid.

What is the json that is being returned from the server?

Regards,
Allan

julioc

Allan,

Thanks for the quick response. I appreciate this! I do not post the code to a page yet. :( Do i need to post the entire code? Just use http://live.datatables.net/ ?

This is the json that comes from the server (this ajax --> ../asp/pesquisaSupervisor.asp):

{
    "options": {
        "Supervisor": [{
            "label": "LUIS",
            "value": "360"
        }, {
            "label": "TATIANE",
            "value": "1030"
        }, {
            "label": "MICHELE",
            "value": "1514"
        }, {
            "label": "FLAVIA",
            "value": "2050"
        }, {
            "label": "LILIANE",
            "value": "4060"
        }, {
            "label": "ELIANE",
            "value": "6030"
        }, {
            "label": "OLIVIA",
            "value": "2015"
        }, {
            "label": "SOFIA",
            "value": "7025"
        }]
    }
}

In the form, after i click at the create button, the select list is fullfilled (it´s ok), but in the console i can see the calls (ajax) to the server many times.

Regards,
Júlio Cézar

allan

What version of Editor are you using? If not 1.5.5, can you update please?

Allan

julioc

Allan,

Actually, it is exactly this version of the Editor (Editor-1.5.5). The DataTables is (DataTables-1.10.11).

Everything is working fine, the select list is loaded (labels and values), except for this problem (many ajax calls to the server from that part of the code - as above). Do i have another way to do the load of labels and values of the select list?

This is what i´m using:

<link rel="stylesheet" type="text/css" href="../css/jquery-ui.css">
<link rel="stylesheet" type="text/css" href="../css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="../css/bootstrap-theme.min.css">
<link rel="stylesheet" type="text/css" href="../DataTables-1.10.11/media/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" type="text/css" href="../DataTables-1.10.11/extensions/buttons/css/buttons.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="../DataTables-1.10.11/extensions/select/css/select.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="../Editor-1.5.5/css/editor.dataTables.min.css">

<script type="text/javascript" src="../js/jquery-1.12.0.min.js"></script>
<script type="text/javascript" src="../js/jquery-ui.min.js"></script>
<script type="text/javascript" src="../js/bootstrap.min.js"></script>
<script type="text/javascript" src="../DataTables-1.10.11/media/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="../DataTables-1.10.11/extensions/buttons/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="../DataTables-1.10.11/extensions/select/js/dataTables.select.min.js"></script>
<script type="text/javascript" src="../DataTables-1.10.11/extensions/buttons/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="../DataTables-1.10.11/extensions/buttons/js/buttons.print.min.js"></script>
<script type="text/javascript" src="../js/jszip.min.js"></script>
<script type="text/javascript" src="../js/pdfmake.min.js"></script>
<script type="text/javascript" src="../js/vfs_fonts.js"></script>
<script type="text/javascript" src="../js/moment.min.js"></script>
<script type="text/javascript" src="../Editor-1.5.5/js/dataTables.editor.min.js"></script>


<script type="text/javascript" src="../js/funcoes.js"></script> --> this one is where i load the editor and datatables.

allan

Can you link to a page showing the issue so I can debug the issue please?

Allan

julioc

Allan,

Here is the link so you can see the problem: http://tjdf199.tjdft.jus.br/rhinter/s234/asp/substituicaoEstagiario.asp

After select any record and click at the "Editar" button, with the console opened, you will see the problem in Network, XHR.

Note: if you try to open the link above and the records do not show, please try again. The server is slow, so sometimes you have to press F5 (reload page) a couple times. Maybe you can see an ajax error, just try F5 (reload page) a couple times, it's working.

Thank you for the support!

Regards,
Júlio Cézar

allan

Hi Júlio,

Thanks for the link - I seethe issue now. The dependent() has been applied to the field Supervisor, and the JSON returned from the server contains:

{
    "options": {
        "Supervisor": [{
            ...
        }]
    }
}

Hence it is updating itself! So if it changes value, it triggers an Ajax call, which writes new options and thus triggers another Ajax call, thus the infinite loop.

What is the intended outcome here? Presumably you don't want the options to be dependent upon the value selected for the same field, but I'm not sure what it is that you are actually looking to do. Are the options dependent upon another field?

Thanks,
Allan

julioc

Hi Allan,

No problem!

Actually, i need that this "Supervisor" select list work as the other two select lists that are into the form. I mean, load the labels and values and mark as selected the value that comes from the database.

The point is that these two select lists have the labels and values specified into the "funcoes.js" file because they are static and i need that the "Supervisor" select list have the labels and values dinamically loaded.

The labels and values will be loaded according to the datatables selected record, bringing the supervisors of that sector and the selected supervisor that comes from the database.

What am i doing wrong? What are the ways to do this?

Thanks for all the support!

Regards,
Júlio Cézar

allan

bringing the supervisors of that sector

Right - so the supervisors field is dependent upon the value of the sector. Is there a field for the sector? You would use something like:

editor.dependent( 'sector', ... ) // listens for changes from the sector and updates the supervisors field

Allan

julioc

Hi Allan,

I did this way:

In the client i have:

var codigo;

$('#example tbody').on( 'click', 'td', function () {
     codigo = table.row( this ).data().loc;      
});

editor.on( 'open', function () {
    $.ajax ({
        type: 'POST',
        url: 'pesquisa.asp?loc='+codigo,
        dataType: 'json',
        success: function (dados) {             
            editor.field('Supervisor').update(dados);
        }           
    });
});

In the server i have:

codigo = request.querystring("loc")

QueryToJSON(DB, getConsulta(codigo)).flush()

When the form is opened, the ajax is executed and the select list is fulfilled. It´s working with only one call to the server.

Thank you for all the support!!!

Regards,
Júlio Cézar