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
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
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).Agreed! The current search is terrible! Its something I'm looking into.
Allan
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
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!
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:
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:Allan
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:
and the current editor.dependent .js
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:
Thanks!
I assume it is the
PermissionToEnter.unit
select list that you want to update? In which case the returned JSON should be:i.e. use the field name. Note that it is case sensitive as well.
Allan
:) Thank you!!
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.
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?
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
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):
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
What version of Editor are you using? If not 1.5.5, can you update please?
Allan
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:
Can you link to a page showing the issue so I can debug the issue please?
Allan
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
Hi Júlio,
Thanks for the link - I seethe issue now. The
dependent()
has been applied to the fieldSupervisor
, and the JSON returned from the server contains: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
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
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:
Allan
Hi Allan,
I did this way:
In the client i have:
In the server i have:
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