jQuery DataTables plugin with PHP and MS SQL

I hereby provided the code for server-side processing for PHP and MS SQL.

The environment I used was PHP 5.5 and MS SQL 2008

Since PHP 5.5 no longer supports mssql extension, hence, I used sqlsrv extension instead on Window.

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
 
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
// add your columns here!!!
$aColumns = array( 'columnName1', 'columnName2' );
 
/* MSSQL Database infomation */
$host = 'localhost';
$connectionInfo = array("Database"=>"yourdb", "UID"=>"yourUser", "PWD"=>"yourPassword", "CharacterSet"=>"UTF-8");
$conn = sqlsrv_connect($host,$connectionInfo);
if($conn){
 //echo "Connection Established";
}else{
 echo "Connection could not be Established";
 die ( 'Can not connect to server' );
}
 
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";
 
/* DB table to use */
$sTable = "Your Table";
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
 
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
 
 
/* Ordering */
$sOrder = "";
if ( isset( $_POST['order'] ) )
{
 $sOrder = "ORDER BY ";
 if ( $_POST['columns'][0]['orderable'] == "true" )
 {
 $sOrder .= "".$aColumns[ intval( $_POST['order'][0]['column'] ) ]." ".
 ($_POST['order'][0]['dir']==='asc' ? 'asc' : 'desc');
 }
}
 
/* escape function */
function mssql_escape($data) {
if(is_numeric($data))
return $data;
$unpacked = unpack('H*hex', $data);
return '0x' . $unpacked['hex'];
}
 
/* Filtering */
$sWhere = "";
if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
$sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_POST['search']['value'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
if ( isset($_POST['columns'][$i]) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
if ( $sWhere == "" ) {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".addslashes($_POST['columns'][$i]['search']['value'])."%' ";
}
}
 
/* Paging */
$top = (isset($_POST['start']))?((int)$_POST['start']):0 ;
$limit = (isset($_POST['length']))?((int)$_POST['length'] ):5;
$sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
FROM $sTable
$sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
(
SELECT TOP $top $sIndexColumn FROM
$sTable $sOrder
)
$sOrder";
$rResult = sqlsrv_query($conn, $sQuery);
if($rResult === false){
 die(sqlsrv_errors(SQLSRV_ERR_ERRORS));
}
 
/* Data set length after filtering */
$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query($conn, $sQueryCnt );
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );
 
/* Total data set length */
$sQuery = "
SELECT COUNT(id)
FROM $sTable
";
$rResultTotal = sqlsrv_query($conn, $sQuery );
$aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);

$iTotal = $aResultTotal[0];
 
 
/* Output */
$output = array(
"draw" => intval($_POST['draw']),
"recordsTotal" => $iTotal,
"recordsFiltered" => $iFilteredTotal,
"data" => array()
);
 
 
while ( $aRow = sqlsrv_fetch_array( $rResult, SQLSRV_FETCH_ASSOC) )
{
 $row = array();
 for ( $i=0 ; $i<count($aColumns) ; $i++ )
 {
 /* General output */
 $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
 }
 $output['data'][] = $row;
}
 
echo json_encode( $output );
 
?>

 

Advertisements

2 thoughts on “jQuery DataTables plugin with PHP and MS SQL

    • In order to make this warning disappear, use these lines.

      $draw = (isset($_POST[‘draw’]))?((int)$_POST[‘draw’]):0 ;
      /* Output */
      $output = array(
      “draw” => $draw,
      “recordsTotal” => $iTotal,
      “recordsFiltered” => $iFilteredTotal,
      “data” => array()
      );

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s