My Universal Simple PHP DataTables Class (should be compatible with any database driver)

DataTables.class.php

<?php

class DataTables {
    public $table_name = "";
    public $columns = array();
    public $wheres = array();

    public $search = "";
    public $filters = array();

    public $orders = array();
    public $start = 0;
    public $length = 0;
    public $draw = 0;

    public $searchHandler = null;
    public $queryExecutor = null;

    public $results = array();
    public $recordsTotal = 0;
    public $recordsFiltered = 0;

    public function __construct ($queryExecutor = null, $table_name = null, $columns = null) {
        if (!is_null($queryExecutor)) {
            $this->setQueryExecutor($queryExecutor);
        }
        if (!is_null($table_name)) {
            $this->setTableName($table_name);
        }
        if (!is_null($columns)) {
            $this->setColumns($columns);
        }
    }

    public function setSearchHandler ($searchHandler) {
        $this->searchHandler = $searchHandler;
        return true;
    }

    public function setQueryExecutor ($queryExecutor) {
        $this->queryExecutor = $queryExecutor;
        return true;
    }

    public function setTableName ($table_name) {
        $this->table_name = $table_name;
        return $this->table_name;
    }

    public function setColumns ($columns) {
        $this->columns = array();
        foreach ($columns as $index => $element) {
            if (!is_array($element)) {
                $index = $element;
                $element = array();
            }
            $this->columns[isset($element["alias"]) ? $element["alias"] : $index] = array(
                "alias" => isset($element["alias"]) ? $element["alias"] : $index,
                "column" => isset($element["column"]) ? $element["column"] : $index,
                "query" => isset($element["query"]) ? $element["query"] : (isset($element["column"]) ? $element["column"] : $index),
                "sortable" => isset($element["sortable"]) ? $element["sortable"] : true
            );
        }
        return $this->columns;
    }

    public function setWheres ($wheres) {
        $this->wheres = $wheres;
        return $this->wheres;
    }

    public function addWhere ($column, $value = null, $operator = null) {
        if ($value === null && $operator === null) {
            $this->wheres[] = array($column, array());
        } elseif ($operator === null && is_array($value)) {
            $this->wheres[] = array($column, $value);
        } elseif ($operator === null) {
            $this->wheres[] = array($column." = ?", array($value));
        } elseif (is_array($value)) {
            $this->wheres[] = array(implode(" AND ", array_fill(0, count($value), $column." ".$operator." ?")), $value);
        } else {
            $this->wheres[] = array($column." ".$operator." ?", array($value));
        }
        return $this->wheres;
    }

    public function setSearch ($search = "") {
        $this->search = trim($search);
        return $this->search;
    }

    public function applySearch ($search = null) {
        if (!is_null($search)) {
            $this->setSearch($search);
        }
        if (is_string($this->search) && strlen($this->search) > 0) {
            call_user_func($this->searchHandler, $this->search);
        }
        return $this->search;
    }

    public function setFilters ($filters) {
        $this->filters = $filters;
        return $this->filters;
    }

    public function addFilter ($column, $value = null, $operator = null) {
        if ($value === null && $operator === null) {
            $this->filters[] = array($column, array());
        } elseif ($operator === null && is_array($value)) {
            $this->filters[] = array($column, $value);
        } elseif ($operator === null) {
            $this->filters[] = array($column." = ?", array($value));
        } elseif (is_array($value)) {
            $this->filters[] = array(implode(" AND ", array_fill(0, count($value), $column." ".$operator." ?")), $value);
        } else {
            $this->filters[] = array($column." ".$operator." ?", array($value));
        }
        return $this->filters;
    }

    public function setOrders ($orders) {
        $this->orders = $orders;
        return $this->orders;
    }

    public function addOrder ($column, $order = "ASC") {
        $this->orders[$column] = strtoupper($order);
        return $this->orders;
    }

    public function setStart ($start = 0) {
        $this->start = $start > 0 ? $start : 0;
        return $this->start;
    }

    public function setLength ($length = 10, $min = 10, $max = 100) {
        $this->length = $length > $min ? ($length < $max ? $length : $max) : $min;
        return $this->length;
    }

    public function setDraw ($draw = 0) {
        $this->draw = $draw > 0 ? $draw : 0;
        return $this->draw;
    }

    public function setParameters ($request) {
        if (isset($request["order"]) && is_array($request["order"])) {
            foreach ($request["order"] as $order) {
                if (isset($order["column"]) && isset($order["dir"]) && is_string($order["column"]) && is_string($order["dir"]) && in_array(strtolower($order["dir"]), array("asc", "desc"))) {
                    if (isset($request["columns"][$order["column"]]["data"]) && is_string($request["columns"][$order["column"]]["data"])) {
                        if (isset($this->columns[strval($request["columns"][$order["column"]]["data"])]) && $this->columns[strval($request["columns"][$order["column"]]["data"])]["sortable"]) {
                            $this->addOrder($this->columns[strval($request["columns"][$order["column"]]["data"])]["column"], $order["dir"]);
                        }
                    }
                }
            }
        }
        $this->setStart(isset($request["start"]) && intval($request["start"]) > 0 ? intval($request["start"]) : 0);
        $this->setLength(isset($request["length"]) && intval($request["length"]) > 0 ? intval($request["length"]) : 0);
        $this->setDraw(isset($request["draw"]) && intval($request["draw"]) > 0 ? intval($request["draw"]) : 0);
        $this->applySearch(isset($request["search"]["value"]) && is_string($request["search"]["value"]) ? trim(strval($request["search"]["value"])) : "");
    }

    public function buildResultQuery () {
        $query = "";
        $params = array();

        $query .= "SELECT ";

        $array = array();
        foreach ($this->columns as $column) {
            if ($column["column"] === $column["query"]) {
                if ($column["column"] === $column["alias"]) {
                    $array[] = $column["column"];
                } else {
                    $array[] = $column["column"]." AS ".$column["alias"];
                }
            } else {
                $array[] = "(".$column["query"].") AS ".$column["alias"];
            }
        }
        $query .= implode(", ", $array);

        $query .= " FROM ".$this->table_name;

        $array = array();
        foreach ($this->wheres as $where) {
            $array[] = $where[0];
            $params = array_merge($params, $where[1]);
        }
        foreach ($this->filters as $where) {
            $array[] = $where[0];
            $params = array_merge($params, $where[1]);
        }
        if (count($array) > 0) {
            $query .= " WHERE ".implode(" AND ", $array);
        }

        $array = array();
        foreach ($this->orders as $column => $order) {
            $array[] = $column." ".$order;
        }
        if (count($array) > 0) {
            $query .= " ORDER BY ".implode(", ", $array);
        }

        if ($this->length > 0) {
            $query .= " LIMIT ".intval($this->length);
        }

        if ($this->start > 0) {
            $query .= " OFFSET ".intval($this->start);
        }

        return array($query, $params);
    }

    public function buildRecordsTotalQuery () {
        $query = "";
        $params = array();

        $query .= "SELECT COUNT(*) AS RecordsTotal FROM ".$this->table_name;

        $array = array();
        foreach ($this->wheres as $where) {
            $array[] = $where[0];
            $params = array_merge($params, $where[1]);
        }
        if (count($array) > 0) {
            $query .= " WHERE ".implode(" AND ", $array);
        }

        return array($query, $params);
    }

    public function buildRecordsFilteredQuery () {
        $query = "";
        $params = array();

        $query .= "SELECT COUNT(*) AS RecordsFiltered FROM ".$this->table_name;

        $array = array();
        foreach ($this->wheres as $where) {
            $array[] = $where[0];
            $params = array_merge($params, $where[1]);
        }
        foreach ($this->filters as $where) {
            $array[] = $where[0];
            $params = array_merge($params, $where[1]);
        }
        if (count($array) > 0) {
            $query .= " WHERE ".implode(" AND ", $array);
        }

        return array($query, $params);
    }

    public function setResults ($results) {
        $this->results = is_array($results) ? $results : array();
        return $this->results;
    }

    public function setRecordsTotal ($recordsTotal) {
        if (isset($recordsTotal[0])) {
            $recordsTotal = array_values($recordsTotal[0])[0];
        }
        $this->recordsTotal = intval($recordsTotal);
        return $this->recordsTotal;
    }

    public function setRecordsFiltered ($recordsFiltered) {
        if (isset($recordsFiltered[0])) {
            $recordsFiltered = array_values($recordsFiltered[0])[0];
        }
        $this->recordsFiltered = intval($recordsFiltered);
        return $this->recordsFiltered;
    }

    public function queryExecute () {
        $this->setResults(call_user_func_array($this->queryExecutor, $this->buildResultQuery()));
        $this->setRecordsTotal(call_user_func_array($this->queryExecutor, $this->buildRecordsTotalQuery()));
        $this->setRecordsFiltered(call_user_func_array($this->queryExecutor, $this->buildRecordsFilteredQuery()));
        return true;
    }

    public function execute () {
        try {
            $this->queryExecute();
            return array(
                "status" => "200",
                "draw" => $this->draw,
                "recordsTotal" => $this->recordsTotal,
                "recordsFiltered" => $this->recordsFiltered,
                "data" => $this->results
            );
        } catch (Throwable|Exception) {
            return array(
                "status" => "500",
                "error" => "InternalServerErrorException",
                "message" => "Internal Server Error."
            );
        }
    }
}

/*

Example: Use DataTables with PostgreSQL

$datatables = new DataTables();
$datatables->setTableName("users");
$datatables->setColumns(array(
    "user_id" => array(),
    "username" => array(),
    "fullname" => array(
        "query" => "COALESCE(NULLIF(fullname, ''), username)"
    ),
    "video_count" => array(
        "query" => "SELECT COUNT(*) FROM videos WHERE owner_id = users.user_id AND status >= 0"
    ),
    "status" => array(
        "sortable" => false
    )
));
$datatables->addWhere("status", 0, ">");
$datatables->setSearchHandler(function ($search_value) use ($datatables) {
    if (preg_match("/^[1-9]+[0-9]*$/", $search_value)) {
        $datatables->addFilter("(user_id = ? OR username ILIKE ? OR fullname LIKE ?)", array($search_value, "%".$search_value."%", "%".$search_value."%"));
    } else {
        $datatables->addFilter("(username ILIKE ? OR fullname ILIKE ?)", array("%".$search_value."%", "%".$search_value."%"));
    }
});
$datatables->setQueryExecutor(function ($query, $params) {
    // Apply Params Replacing for pg_ functions.
    $param_index = 0;
    $query = preg_replace_callback("/\?/", function () use (&$param_index) {
        return "$".++$param_index;
    }, $query);
    $result = pg_query_params($GLOBALS["db"], $query, $params);
    return $result ? pg_fetch_all($result) : array();
});
$datatables->setParameters($_REQUEST);
$response = $datatables->execute();

*/

?>