Re-writing a SQL View Query for DataTables AJAX Server-Side Processing

stuartsjb-icr

I'm having some difficulty understanding how to re-write a View's complex SQL query to PHP to make it compatible with DataTables' AJAX server-side processing.

While simply copying the TSQL from the View to a $sql variable in a PHP file to return a JSON array (using the aaData and aoColumns options in the DataTable initialisation) works for providing client-side functionality...

$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
$outputArray = (array stuff here)
}
echo json_encode($outputArray);

...it is very slow to generate the many rows (presumably due to our PHP or SQL server running through the query). Additionally, I need to be able to edit some of the fields returned by the PHP, and was hoping to use the same AJAX PHP for both Datatables and Editor as I have previously for a different table.

I've created a server-side PHP script for DataTables before using the Editor, Field and Join classes (and was also populating Select lists for the Editor window from SQL), but converting this View to work has me quite stumped as it involves Case statements, Casts and other logic to generate the data, and I'm not sure how to begin converting it.

Is there any in-depth documentation on server-side scripting somewhere on the site that I've missed?

allan

hi,

Server-side processing's requirements are documented in the manual. That basically describes the parameters sent, and what is returned. How exactly you get from one to the other is left to the implementation.

You note you have used Editor for server-side processing in the past - have you considered using Editor's libraries for server-side processing here as well? If you don't want editing on the table, just don't present the editing controls to the end user. Would that work for you?

Regards,
Allan