Trying to replicate Cascading lists in Editor
Trying to replicate Cascading lists in Editor
cpshart
Posts: 246Questions: 49Answers: 5
Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I am trying to replicate the blog post Cascading lists in Editor, https://datatables.net/blog/2017-09-01, as I need to integrate the same functionality on my main system. I have must be very close, I have the following set-up.
I have setup MySQL tables, countries, continents and added some test data.
The issue is that the editor does not show a dropdown list of countries (the dropdown list is empty) on selection of the continent, however the JSON returned from the server is the same format as your blog post and contains the countries associated with the continent.
client file
<body>
<table id="sales-location" class="display" width="100%">
<caption style="caption-side: bottom; text-align: center;">Sales team</caption>
<thead>
<tr>
<th rowspan="2">Name</th>
<th colspan="2">Location</th>
</tr>
<tr>
<th>Continent</th>
<th>Country</th>
</tr>
</thead>
</table>
<script type="text/javascript">
function init ( json ) {
var editor = new $.fn.dataTable.Editor( {
table: '#sales-location',
fields: [
{
label: 'Name:',
name: 'name'
},
{
label: 'Continent:',
name: 'continent',
type: 'select',
options: json.continentOptions
},
{
label: 'Country:',
name: 'country',
type: 'select' }
]
} );
editor.dependent( 'continent', function ( val, data ) {
$.ajax( {
url: '../../Editor-PHP-1.9.4/controllers/countries.php',
datatype: 'html',
// pass continent value to server php script
data: { "continent": val }
});
});
$('#sales-location').DataTable( {
dom: 'Bfrtip',
data: json.team,
columns: [
{ data: 'name' },
{
data: 'continent',
render: function ( d ) {
return json.continent[ d ];
}
},
{
data: 'country',
render: function ( d ) {
return json.country[ d ];
}
}
],
select: {
style: 'single'
},
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
]
} );
}
$(document).ready( function () {
$.ajax( {
url: 'https://dividendlook.co.uk/media/blog/2017-09-01/data',
dataType: 'json',
success: function ( json ) {
init( json );
}
} );
}(jQuery));</script>
</body>
</html>
server file
<?php
/*
* /home/ukincome/public_html/Editor-PHP-1.9.4/controllers/countries.php
*/
/* Loads the WordPress environment and template */
require( '../../wp-blog-header.php' );
global $current_user;
wp_get_current_user();
// DataTables PHP library
include( "../lib/DataTables.php" );
$continent = $_GET['continent'];
$countries_array = 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);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
$result = $pdo->query("SELECT 'country', id, name FROM countries WHERE continent_id = $continent");
foreach ($result as $row) {
array_push( $countries_array, array( 'value'=>$row['id'], 'label'=>$row['name'] ) );
}
echo json_encode( [
'options' => [
'country' => $countries_array
]
] );
JSON returned on selection of continent, shows correct countries
data XHRcountries.php?continent=1 XHRcountries.php?continent=3
2 different selections works
{"options":{"country":[{"value":"1","label":"Algeria"},{"value":"2","label":"Angola"},{"value":"3","label":"Benin"},{"value":"4","label":"Botswana"},{"value":"5","label":"Burkina"},{"value":"6","label":"Burundi"},{"value":"7","label":"Cameroon"},{"value":"8","label":"Cape Verde"},{"value":"9","label":"Central African Republic"},{"value":"10","label":"Chad"},{"value":"11","label":"Comoros"},{"value":"12","label":"Congo"}]}}
I can provide remote access to my system by PM if required, details of setup files is below
https://www.dividendlook.co.uk/wp-admin/post.php?post=25916&action=edit
https://www.dividendlook.co.uk/test-cascade-lists-tables/
/home/ukincome/public_html/Editor-PHP-1.9.4/controllers/countries.php
Many Thanks for any help.
Colin
This question has an accepted answers - jump to answer
Answers
I have not solved the problem, as I need to perform this dynamically, but if I save for example the JSON returned by the server when continent = 2, Asia as follows
Save the JSON contents below in file
Then add the code above $('#sales-location').DataTable( {, at say Line 47 above in the client file
and the country dropdown shows the correct countries
My question is how can I perform this dynamically at run time.
Many Thanks Colin
Reformatting your
dependent()call a little:I can see that you aren't doing anything with the data coming back from your Ajax call to the server. Try this which executes the
callbackof the custom function:Allan
Hi Allan
Thanks for your response, it makes sense I have just tried changing the code as suggested above, but the Country Select list is still empty.
I can provide access to my system if required, you should have the details from previous calls, if not I can PM the details.
The Country select is still not reading the original JSON file
Best Regards
Colin
Hi Allan
I have successfully integrated the cascade functionality into my system, despite not completing this test system, I may come back to this and post a working system if that helps others.
Thanks for your help.
Colin
cpshart - can you please post what you changed to make this work? thanks
Hello - does anyone have any suggestions? I've duplicated everything posted by cpshart and have run into the same issue he had where the country select is not populated via an ajax call. I was able to verify that ajax was passing the correct information and format to populate the options. I even copied/pasted those results to a file and called that file in place of the ajax fetch and it worked fine.
So I know the results of the ajax fetch are good but for some reason they're not used to populate the country select. I've tried Allan's suggestion on reformatting the dependent call (along with everything else I can think of) but still no luck. Thanks in advance for any help.
Could you post your code and the ajax response, please, so we can take a look,
Colin
Thanks for the quick response Colin. Out of frustration/deadlines I ended up modifying my app to skirt around using dependent(). I'll recreate it when I get some cycles in case anyone else is having the same issue. Happy New Year.
Update - I went back and got it working. In my case the solution was to add this to my php file that ajax was calling:
header('Content-Type: application/json');
Hope it helps.
Nice one - thanks for posting back!
Allan
Hi windbill/allan/colin
Apologies for not getting back, I was offline with datatables for the last month as it was registered to my previous email address, now updated..
windbill, I am pleased that you got your cascade system working ok.
Best Regards
Colin