Raw SQL to source data for Datatable

Raw SQL to source data for Datatable

DT ProgerDT 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

  • allanallan Posts: 52,276Questions: 1Answers: 7,963 Site admin
    Answer ✓

    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 ProgerDT Proger Posts: 18Questions: 5Answers: 0

    Thanks Allan. Your solution works perfectly.

This discussion has been closed.