Raw SQL to source data for Datatable
Raw SQL to source data for Datatable
DT Proger
Posts: 18Questions: 5Answers: 0
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.
This question has an accepted answers - jump to answer
Answers
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
dataproperty, although that can be controlled usingajax.dataSrcand, 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:
would match the defaults of DataTables.
Regards,
Allan
Thanks Allan. Your solution works perfectly.