column.data property using dynamic JSON result.

bbrindza

I really could use some help on this.

I have an application that is using JQuery Datatable, server side processing using AJAX .
I use a drop-down selection that will allow a user to choose the fields they what to see in the table.
Seeing that this design lends itself to variable table columns, I need to be able to set the column.data property with the dynamic results of the DataTables column identifier from the server-side processing JSON.

How can this be achieved?

Please note that I’m still new to advanced JQuery Datatable concepts. I exhausted all my knowledge and internet searches trying to find the right solution.

column.data property....

Need this to be dynamic.

  columns: [    
            { data: "Employee Number" },
            { data: "First Name" },
            { data: "Last Name" },
            { data: "Hire Date" },
            { data: "Annual Salary" },
            { data: "Hourly Salary" },
            { data: "Title" }, 
            { data: "Birth Date" },
            { data: "Department" }...etc.
        ],

serve-side processing JSON (sample)

{"draw":0,"recordsTotal":20,"recordsFiltered":20,"data":[{"Employee Number":"1","First name":"CHARLES ","Hire Date":"19650801","Title":"167","Department":"1110"},{"Employee Number":"5","First Name":"NANCY E. ","Hire date":"19970303","Title":"194","Department":"2410"},{"Employee Number":"11","First Name":"CELESTE A ","Hire Date":"19970717","Title":"194","Department":"2410"},{"Employee Number":"12","First Name":"ANA ","Hire Date":"19870526","Title":"114","Department":"1224"},{"Employee Number":"13","First Name":"CHRISTOPHER M. ","Hire Date":"19770626","Title":"213","Department":"2075"},{"Employee Number":"15","First Name":"DANIEL W. ","Hire Date":"19861027","Title":"213","Department":"2310"},{"Employee Number":"17","First Name":"JAMES ","Hire Date":"19790416","Title":"257","Department":"2310"},{"Employee Number":"21","First Name":"ALAN ","Hire Date":"19800225","Title":"75","Department":"2410"},{"Employee Number":"23","First Name":"JEAN E. ","Hire Date":"19940921","Title":"77","Department":"2410"},{"Employee Number":"24","First Name":"JANE ","Hire Date":"19970324","Title":"173","Department":"1213"},{"Employee Number":"27","First Name":"BRUCE A. ","Hire Date":"19850722","Title":"213","Department":"2310"},{"Employee Number":"29","First Name":"THOMAS ","Hire Date":"19780313","Title":"74","Department":"1212"}]}

Sample code
//DataTables Load
$("#PIMLookupTable" ).dataTable( {

LengthMenu: [[25, 50, 100, 250, 500, -1], [25, 50, 100, 250, 500, "All"]],
iDisplayLength: 15,
sPaginationType: "full_numbers",
oLanguage: {
       sSearch: "Table Search: "},
processing: true,
serverSide: true,

ajax: {url:  "ssp_PIM_EmployeeLookup.php",
          data: {locationCode: 'HEA',
                    fieldsSelected: fieldSelectionDataString }
      },

columns: [    
            { data: "Employee Number" },
            { data: "First Name" },
            { data: "Last Name" },
            { data: "Hire Date" },
            { data: "Annual Salary" },
            { data: "Hourly Salary" },
            { data: "Title" }, 
            { data: "Birth Date" },
            { data: "Department" }
        ],
colReorder: true

});//END .dataTable

SSP Script PHP

```php
<?php

$fieldsSelected = json_decode(($_GET['fieldsSelected']), true);
$locationCode = $_GET['locationCode'];

//Test array
$fieldsSelected = array(array('fieldName' => 'EMP#', 'fieldDescription' => 'Employee Number' ),
array('fieldName' => 'NAMEF', 'fieldDescription' => 'First Name'),
array('fieldName' => 'HIRDAT','fieldDescription' => 'Hire Date'),
array('fieldName' => 'TITCD', 'fieldDescription' => 'Title'),
array('fieldName' => 'DEPT', 'fieldDescription' => 'Department'),
);
$locationCode ='HEA';

include('i5db2connect.php');

// DB table to use
$table = "XXXLIB.XXXFILE where LOCID = '{$locationCode}'";

// Table's primary key
$primaryKey = 'EMP#';

// Array of database columns which should be read and sent back to DataTables.
// The db parameter represents the column name in the database, while the dt
// parameter represents the DataTables column identifier. In this case simple
// indexes

foreach ($fieldsSelected as $key => $fields) {

 $fieldName=trim($fieldsSelected[$key]['fieldName']);

 $fieldHeading=trim($fieldsSelected[$key]['fieldDescription']);

  $columns[] = array('db' => $fieldName, 'dt'=> $fieldHeading);

} //End - foreach ($fieldsSelected as $key => $fieldName)

require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $connection, $table, $primaryKey, $columns )
)

<?php > ``` Thanks to all in the community! Bob ?>

allan

Hi Bob,

I think this is actually covered by the dynamic columns FAQ:

Q. Can I define my columns in Ajax loaded JSON?.

Its more difficult if you need to use server-side processing - you'd need an Ajax request to get the columns and then another for the first page of data.

Allan

bbrindza

Thanks Allan . Works fine now.