I want to create a cascade dropdown on jTable using PHP on the server side. I've been following the demo provided in the API, but despite my efforts I don't seem to get it working. Using firebug I found out the AJAX request to fill the second dropdown is not being made. Anyway, here's part of the code I have. Hope you can throw some light on this.
The first dropdown is called "Region" and the second one "Trabajo", depending on the Region I want to pull from the database the workplaces that belong to it.
<script type="text/javascript">
$(document).ready(function () {
//Prepare jTable
$('#PeopleTableContainer').jtable({
title: 'Colaboradores',
paging: true,
sorting: true,
defaultSorting: 'no_empleado ASC',
actions: {
listAction: 'usuariosActions.php?action=list',
createAction: 'PersonActionsPagedSorted.php?action=create',
updateAction: 'PersonActionsPagedSorted.php?action=update'
},
fields: {
no_empleado: {
title: 'Numero de colaborador',
key: true,
edit: false,
},
nombres: {
title: 'Nombres',
},
apellido_paterno: {
title: 'Apellido paterno',
},
apellido_materno: {
title: 'Apellido materno',
},
genero: {
title: 'Genero',
type: 'radiobutton',
options:{'0':'FEMENINO','1':'MASCULINO'},
list:false
},
fecha_nacimiento: {
title: 'Fecha de nacimiento',
type: 'date',
displayFormat: 'yy-mm-dd',
list:false
},
estado_civil: {
title: 'Estado civil',
options:{ '-1':'','1': 'CASADO', '2': 'DIVORCIADO', '3': 'SOLTERO','4':'UNION LIBRE','5':'VIUDO' },
list:false
},
dependientes: {
title: 'Número de dependientes',
options:{ '-1':'','0':'0','1': '1', '2': '2', '3': '3','4':'4','5':'5','6':'6','7':'7','8':'8','9':'9','10':'10','11':'11','12':'12','13':'13','14':'14','15':'15','16':'16','17':'17','18':'18','19':'19','20':'20' },
list:false
},
seguro_social: {
title: 'Número de seguridad social',
list:false
},
RFC: {
title: 'RFC',
list:false
},
calle: {
title: 'Calle',
list:false
},
no_ext: {
title: 'No. Ext',
list:false
},
no_int: {
title: 'No. Int',
list:false
},
colonia: {
title: 'Colonia',
list:false
},
mun_del: {
title: 'Del/Mun',
list:false
},
zip: {
title: 'Zip',
list:false
},
telefono: {
title: 'Telefono',
list:false
},
entidad_federativa: {
title: 'Entidad federativa',
list:false
},
fecha_antiguedad: {
title: 'Fecha ingreso',
type: 'date',
displayFormat: 'yy-mm-dd',
list:false
},
fecha_puesto: {
title: 'Fecha de inicio en el puesto',
type: 'date',
displayFormat: 'yy-mm-dd',
list:false
},
escolaridad: {
title: 'Escolaridad',
options:{ '-1':'','1': 'PRIMARIA', '2': 'SECUNDARIA', '3': 'PREPARATORIA','4':'UNIVERSIDAD','5':'ESPECIALIDAD','6':'POSGRADO','7':'MAESTRIA' },
list:false
},
puesto: {
title: 'Puesto',
list:false
},
contrato: {
title: 'Contrato',
options: {'-1':'','1':'Contrato por Tiempo Indeterminado','2':'Contrato por Tiempo Determinado','3':'Contrato por Obra Determinada','4':'Contrato por Tiempo Determinado'},
list:false
},
ocupacion:{
title: 'Ocupacion',
type: 'textarea',
list:false
},
region: {
title: 'Región',
options: 'usuariosActions.php?action=region',
list:false
},
trabajo: {
title: 'Centro de trabajo',
dependsOn: 'region',
options: function(data){
if(data.source == 'list'){
return 'usuariosActions.php?action=workplaces®ion=0';
}
return 'usuariosActions.php?action=workplaces®ion=' + data.dependedValues.region;
},
list:false
}
}
});
$('#LoadRecordsButton').click(function (e) {
e.preventDefault();
$('#PeopleTableContainer').jtable('load', {
no_empleado: $('#no_empleado').val()
});
});
//Load person list from server
$('#LoadRecordsButton').click();
});
</script>
And here's the PHP code
<?php
require_once("dbconnect.php");
if($_GET["action"] == "list")
{
//Get record count
if(isset($_POST['no_empleado']) && $_POST['no_empleado'] != ""){
$sql_result = 'SELECT
c.`no_empleado`,
c.`nombres`,
c.`apellido_paterno`,
c.`apellido_materno`,
c.`fecha_nacimiento`,
c.`fecha_antiguedad`,
c.`fecha_puesto`,
c.`estado_civil`,
c.`dependientes`,
c.`seguro_social`,
C.`escolaridad`,
C.`puesto`,
C.`RFC`,
C.`genero`,
C.`contrato`,
C.`ocupacion`,
C.`calle`,
C.`no_int`,
C.`no_ext`,
C.`colonia`,
C.`zip`,
C.`mun_del`,
C.`telefono`,
C.`entidad_federativa`,
c.`id_workplace` as trabajo,
r.`id_region` as region
FROM `colaboradores` AS c
INNER JOIN `workplaces` AS w
ON w.`id_workplace` = c.`id_workplace`
INNER JOIN `regiones` AS r
ON r.`id_region` = w.`id_region`
WHERE `no_empleado`= '.$_POST["no_empleado"].';';
$sql_count = 'SELECT COUNT(*) AS RecordCount FROM `colaboradores` WHERE `no_empleado`= '.$_POST["no_empleado"].';';
}
else{
$sql_result = 'SELECT
c.`no_empleado`,
c.`nombres`,
c.`apellido_paterno`,
c.`apellido_materno`,
c.`fecha_nacimiento`,
c.`fecha_antiguedad`,
c.`fecha_puesto`,
c.`estado_civil`,
c.`dependientes`,
c.`seguro_social`,
C.`escolaridad`,
C.`puesto`,
C.`RFC`,
C.`genero`,
C.`contrato`,
C.`ocupacion`,
C.`calle`,
C.`no_int`,
C.`no_ext`,
C.`colonia`,
C.`zip`,
C.`mun_del`,
C.`telefono`,
C.`entidad_federativa`,
r.`id_region` as region,
c.`id_workplace` as trabajo
FROM `colaboradores` AS c
INNER JOIN `workplaces` AS w
ON w.`id_workplace` = c.`id_workplace`
INNER JOIN `regiones` AS r
ON r.`id_region` = w.`id_region`
ORDER BY ' . $_GET["jtSorting"] . ' LIMIT ' . $_GET["jtStartIndex"] . ',' . $_GET["jtPageSize"] . ';';
$sql_count = 'SELECT COUNT(*) AS RecordCount FROM `colaboradores`';
}
$result = $mysqli->query($sql_count);
$row = $result->fetch_assoc();
$recordCount = $row['RecordCount'];
//Get records from database
$result = $mysqli->query($sql_result);
//Add all records to an array
$rows = array();
while($row = $result->fetch_assoc())
{
$rows[] = $row;
}
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['TotalRecordCount'] = $recordCount;
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);
}
if($_GET["action"] == "region"){
$sql_count = 'SELECT `id_region` as Value ,`nombre_region` as DisplayText FROM `regiones`;';
$result = $mysqli->query($sql_count);
$rows = array();
while($row = $result->fetch_assoc()){
$rows[] = $row;
}
//Get records from database
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Options'] = $rows;
print json_encode($jTableResult);
}
if($_GET["action"] == "workplaces"){
$region=$_GET['region'];
if ($region == "")
$result = $mysqli->query("SELECT `id_workplace` as Value, `nombre_workplace` as DisplayText FROM `workplaces` ");
else
$result = $mysqli->query("SELECT `id_workplace` as Value, `nombre_workplace` as DisplayText FROM `workplaces` WHERE `id_region` = '".$region."'");
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Options'] = $rows;
print json_encode($jTableResult);
}
I was right, dependsOn was not sending any sort of event signal and therefore the ajax call wasn't being made. I was using the min.js that came with the zip but it wasn't the newest version, 2.4.0, it was 2.2.0. it seems this functionality was not implemented yet on that version or was buggy.
Anyway, stick to version 2.4.0 and make sure to take a look into the js version if you run into trouble.