Use PHP to create SQL Query, then pass to DataTables

aallard

I have an existing PHP class that performs a very complex query (joining over 8 tables). I would like to use the resultset from this query and send it to DataTables. Is there an easy way for me use my existing php?

ignignokt

The example below is a little bit of my DataTables server-side code that I will share. I also have to use very complicated queries, and make well over 20 tables that are all very different so something like this was very much needed. You can simply build the SQL as a string then send it to the DataTables class. Then you can make your super query into a subquery so it still works with all the DataTables magic (letting sorting, searches, etc work). Be sure in your filter function you protect yourself against SQL injection.

I also built a dynamic function passing method to change formats of dates, numbers, create URLs, insert images, buttons, etc since I find it much easier doing all my data modification on the server-side as well. Use their class file, and this as an example and build your own class file to work however you need it to. The only requirement is by the end of it you return the correct data format for the DataTables front end.

//controller ajax call function
$baseSql = "My super query here";
echo json_encode(
    SSP::buildTable($this->Model,$_REQUEST,$baseSql)
);

//DataTables class file

static function buildSubquery($baseSql){
    return "SELECT * FROM (".$baseSql.")b";
}

static function buildTable($model,$request,$baseSql){
    $sql        = self::buildSubquery($baseSql);
    $where  = self::filter($model,$request);
    $order  = self::order($request);
    $limit  = self::limit($request);
    $data   = $model->runDatatablesSql($sql.$where.$order.$limit);
    return array(
        "draw"          => intval($request['draw']),
        "recordsTotal"      => intval($model->datatablesGetCount($sql)),
        "recordsFiltered"   => intval($model->datatablesGetCount($sql,$where)),
        "data"          => $data
    );
}

//model

function runDatatablesSql($sql){
    $res        = $this->query($sql);
    $return = array();
    while($row = $this->fetch_array($res)){
        $return[] = $row;
    }
    if(!empty($return)) return $return;
    return false;
}

function datatablesGetCount($baseSql,$where=NULL){
    $sql = "
        SELECT 
            count(*) as Count 
        FROM
            ($baseSql";
    if($where){
        $sql.=" $where";
    }
    $sql.=") f";
    $res        = $this->query($sql); //run query
    if($res) return $res['Count'];
    return false;
}

reno1979

Looks like a good solution, but I don't understand how you use $model (create it etc) and it seems to me that you also modified the order, limit and filter functions inside the SSP class.

Could you please give a more detailed example?

ignignokt

I submitted a more complete example to github here