phpcodeigniterdatatabledatatables

Codeigniter - $sOrder and $sLimit in datatables


I'm retrieving records using Codeigniter and datatables. Removing $sOrder and $sLimit loads the data but on filtering, there's a database error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''desc' LIMIT '0', '10'' at line 5

SELECT SQL_CALC_FOUND_ROWS id, FName, LName, status, authorizedby, userName
FROM users 
ORDER BY id 'desc' 
LIMIT '0', '10'"

Here's the code:

        if (isset($_REQUEST['iSortCol_0'])) {
            $sOrder = "ORDER BY  ";
            for ($i = 0; $i < intval($_REQUEST['iSortingCols']); $i++) {
                if ($_REQUEST['bSortable_' . intval($_REQUEST['iSortCol_' . $i])] == "true") {
                    $sOrder .= $aColumns[intval($_REQUEST['iSortCol_' . $i])] . "
                    " . $this->db->escape($_REQUEST['sSortDir_' . $i]) . ", ";
                }
            }
            $sOrder = substr_replace($sOrder, "", -2);
            if ($sOrder == "ORDER BY") {
                $sOrder = "";
            }
        }


        $sWhere = "";
        // this for search code
        if ($_REQUEST['sSearch'] != "") {
            $sWhere = "WHERE (";
            for ($i = 0; $i < count($aColumns); $i++) {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $this->db->escape($_REQUEST['sSearch']) . "%' OR ";
            }
            $sWhere = substr_replace($sWhere, "", -3);
            $sWhere .= ')';
        }


        for ($i = 0; $i < count($aColumns); $i++) {
            if ($_REQUEST['bSearchable_' . $i] == "true" && $_REQUEST['sSearch_' . $i] != '') {
                if ($sWhere == "") {
                    $sWhere = "WHERE ";
                } else {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i] . " LIKE '%" . $this->db->escape($_REQUEST['sSearch_' . $i]) . "%' ";
            }
        }

        // generate sql query 
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aResultColumns)) . "
       FROM   $sTable
       $sWhere
       $sOrder
       $sLimit 
    ";

Now, removing the last two lines ($sOrder and $sLimit) works for loading the data but error on filtering. How is this fixable.


Solution

  • This is how I solved it:

            $sWhere = "";
            // this for search code
            if ($_REQUEST['sSearch'] != "") {
                $sWhere = "WHERE (";
                for ($i = 0; $i < count($aColumns); $i++) {
                    $sWhere .= $aColumns[$i] . " LIKE '*%" . $this->db->escape($_REQUEST['sSearch']) . "%*' OR ";
                }
                $sWhere = substr_replace($sWhere, "", -3);
                $sWhere .= ')';
            }
    
    
            for ($i = 0; $i < count($aColumns); $i++) {
                if ($_REQUEST['bSearchable_' . $i] == "true" && $_REQUEST['sSearch_' . $i] != '') {
                    if ($sWhere == "") {
                        $sWhere = "WHERE ";
                    } else {
                        $sWhere .= " AND ";
                    }
                    $sWhere .= $aColumns[$i] . " LIKE '*%" . $this->db->escape($_REQUEST['sSearch_' . $i]) . "%*' ";
                }
            }
             
            $sOrder = str_replace("'", "", $sOrder);
            $sLimit = str_replace("'", "", $sLimit);
            $sWhere = str_replace("'", "", $sWhere);
            $sWhere = str_replace("*", "'", $sWhere);
    
            // generate sql query 
            $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aResultColumns)) . "
           FROM   $sTable
           $sWhere
           $sOrder
           $sLimit 
        ";```