An updated server side processing script for jQuery DataTables with an Oracle Database and PDO

server_processing.php

<?php
include '../db/connect.php';

class TableData

{
  private $_db;

  // because we need to sort case insensitive and numeric doesnt work

  private $_intcols;
  public function __construct($conn, $numbercols)
  {
    $this->_db = $conn;
    $this->_intcols = $numbercols;
  }

  public

  function get($table, $index_column, $columns)
  {
    // Paging
    $sLimit = "";
    if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1')
    {
      $sLimit = "OFFSET " . intval($_GET['iDisplayStart']) . " ROWS FETCH NEXT " . intval($_GET['iDisplayLength']) . " ROWS ONLY";
    }

    // Ordering
    $sOrder = "";
    if (isset($_GET['iSortCol_0']))
    {
      $sOrder = "ORDER BY  ";
      for ($i = 0; $i < intval($_GET['iSortingCols']); $i++)
      {
        if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i]) ] == "true")
        {
          $sortDir = (strcasecmp($_GET['sSortDir_' . $i], 'ASC') == 0) ? 'ASC' : 'DESC';
          if (in_array($columns[intval($_GET['iSortCol_' . $i]) ], $this->_intcols))
          {
            $sOrder.= "" . $columns[intval($_GET['iSortCol_' . $i]) ] . " " . $sortDir . ", ";
          }
          else
          {
            $sOrder.= "lower(" . $columns[intval($_GET['iSortCol_' . $i]) ] . ") " . $sortDir . ", ";
          }
        }
      }

      $sOrder = substr_replace($sOrder, "", -2);
      if ($sOrder == "ORDER BY lower(" || $sOrder == "ORDER BY")
      {
        $sOrder = "";
      }
    }

    /*
    * Filtering
    * NOTE this does not match the built-in DataTables filtering which does it
    * word by word on any field. It's possible to do here, but concerned about efficiency
    * on very large tables, and MySQL's regex functionality is very limited
    */
    $sWhere = "";
    if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
    {
      $sWhere = "WHERE (";
      for ($i = 0; $i < count($columns); $i++)
      {
        if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true")
        {
          $sWhere.= "" . $columns[$i] . " LIKE :search OR ";
        }
      }

      $sWhere = substr_replace($sWhere, "", -3);
      $sWhere.= ')';
    }

    // Individual column filtering

    for ($i = 0; $i < count($columns); $i++)
    {
      if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '')
      {
        if ($sWhere == "")
        {
          $sWhere = "WHERE ";
        }
        else
        {
          $sWhere.= " AND ";
        }

        $sWhere.= "" . $columns[$i] . " LIKE :search" . $i . " ";
      }
    }

    // SQL queries get data to display
    $sQuery = "SELECT " . str_replace(" , ", " ", implode(", ", $columns)) . " FROM " . $table . " " . $sWhere . " " . $sOrder . " " . $sLimit;
    $statement = $this->_db->prepare($sQuery);

    // Bind parameters
    if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
    {
      $statement->bindValue(':search', '%' . $_GET['sSearch'] . '%', PDO::PARAM_STR);
    }

    for ($i = 0; $i < count($columns); $i++)
    {
      if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '')
      {
        $statement->bindValue(':search' . $i, '%' . $_GET['sSearch_' . $i] . '%', PDO::PARAM_STR);
      }
    }

    $statement->execute();
    $rResult = $statement->fetchAll();
    $iFilteredTotalSql = 'SELECT count(*) over() as anzahl FROM ' . $table . " " . $sWhere . " " . $sLimit;
    $iFilteredTotalQuery = $this->_db->prepare($iFilteredTotalSql);

    // Bind parameters
    if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
    {
      $iFilteredTotalQuery->bindValue(':search', '%' . $_GET['sSearch'] . '%', PDO::PARAM_STR);
    }

    for ($i = 0; $i < count($columns); $i++)
    {
      if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '')
      {
        $iFilteredTotalQuery->bindValue(':search' . $i, '%' . $_GET['sSearch_' . $i] . '%', PDO::PARAM_STR);
      }
    }

    $iFilteredTotalQuery->execute();
    $iFilteredTotal = $iFilteredTotalQuery->fetch() ['ANZAHL'];

    // Get total number of rows in table
    $sQuery = "SELECT COUNT(" . $index_column . ") FROM " . $table . "";
    $iTotal = current($this->_db->query($sQuery)->fetch());

    // Output
    $output = array(
      "sEcho" => (isset($_GET['sEcho'])) ? intval($_GET['sEcho']) : 0,
      "iTotalRecords" => $iTotal,
      "iTotalDisplayRecords" => $iFilteredTotal,
      "aaData" => array()
    );

    // Return array of values
    foreach($rResult as $aRow)
    {
      $row = array();
      for ($i = 0; $i < count($columns); $i++)
      {
        if ($columns[$i] == "version")
        {
          // Special output formatting for 'version' column
          $row[] = ($aRow[$columns[$i]] == "0") ? '-' : $aRow[$columns[$i]];
        }
        else
        if ($columns[$i] != ' ')
        {
          $row[] = $aRow[$columns[$i]];
        }
      }

      $output['aaData'][] = $row;
    }

    echo json_encode($output);
  }
}

header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');

// Create instance of TableData class
$table_data = new TableData($conn, array(
  'MITARBEITERID',
  'KOSTENSTELLE'
));

// Get the data
$table_data->get('MITARBEITER', 'MITARBEITERID', array(
  'MITARBEITERID',
  'VORNAME',
  'NACHNAME',
  'EMAIL',
  'KOSTENSTELLE'
));
?>