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

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

AlchetecAlchetec Posts: 14Questions: 5Answers: 0
edited February 2016 in DataTables

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!

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • AlchetecAlchetec Posts: 14Questions: 5Answers: 0

    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!

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • AlchetecAlchetec Posts: 14Questions: 5Answers: 0
    edited February 2016

    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);
    }
    
    <?php
    >
    ?>
    
    
    

    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!

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Answer ✓

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

  • AlchetecAlchetec Posts: 14Questions: 5Answers: 0

    :) Thank you!!

  • juliocjulioc Posts: 7Questions: 1Answers: 0
    edited March 2016

    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?

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • juliocjulioc Posts: 7Questions: 1Answers: 0

    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

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

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

    Allan

  • juliocjulioc Posts: 7Questions: 1Answers: 0

    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.
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

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

    Allan

  • juliocjulioc Posts: 7Questions: 1Answers: 0
    edited April 2016

    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

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • juliocjulioc Posts: 7Questions: 1Answers: 0

    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

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Answer ✓

    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

  • juliocjulioc Posts: 7Questions: 1Answers: 0

    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

This discussion has been closed.