Raw SQL to source data for Datatable

DT Proger

I have been struggling to find a complete working example that demonstrate the use of raw SQL such as the following to get data for the Datatable (I just need an extremely simple working example and am not looking for anything with any complexity, but couldn't find one yet):

$data = $db->sql( "SELECT * FROM table" )->fetchAll();

I have tried to do up a simple case using that technique, but it does not work. Please see the link below for the test case:

http://58.64.211.82/Ed/examples/advanced/rawSQL.html

The server-side script, rawSQL.php is as simple as the following:

<?php

// DataTables PHP library
include( "../../php/DataTables.php" );

use
DataTables\Database,
DataTables\Database\Query,
DataTables\Database\Result;

$data = $db->sql( "SELECT * FROM products" )->fetchAll();
echo json_encode($data);

Please could you let me know what corrections need to be made to my code in order to make it work?

Thank you in advance.

allan

Hi,

We talked about this by e-mail, but incase any one else is interested, the above code is returning a flat array of data objects. By default DataTables expects an object with a data property, although that can be controlled using ajax.dataSrc and, in this case, setting it to be an empty string to tell it to expect a flat array.

Another option I didn't mention in the e-mail is to modify the returned data structure:

echo json_encode( [
  "data" => $data
] );

would match the defaults of DataTables.

Regards,
Allan

DT Proger

Thanks Allan. Your solution works perfectly.