Use PHP to create SQL Query, then pass to DataTables

Use PHP to create SQL Query, then pass to DataTables

aallardaallard Posts: 2Questions: 2Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39
    edited December 2014 Answer ✓

    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;
    }
    
  • reno1979reno1979 Posts: 6Questions: 0Answers: 0
    edited January 2015

    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?

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39

    I submitted a more complete example to github here

This discussion has been closed.