DATA TABLES using PHP/MSSQL 2000/2005/2008

johnniem

Hey guys,
I dont know how helpful this will be, but i am running a PHP/MSSQL environment for some of my systems, and this DATA TABLE tool was way too good for me to pass it up and not try to use it on my platform. here is my working code. Just the server processing page, everyting else remains the same.

[code]
/* MSSQL connection */
require('includes/connect.php');
//i used am include for my connection

//Count of all records
$cQuery = "SELECT COUNT(*) as total_count FROM table_name";
$rResultTotal = sqlsrv_query($conn,$cQuery);
$aResultTotal = sqlsrv_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
//print_r ($aResultTotal);


/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "TOP ".( $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<( $_GET['iSortingCols'] ) ; $i++ )
{
$sOrder .= fnColumnToField(( $_GET['iSortCol_'.$i] ))."
".( $_GET['sSortDir_'.$i] ) .", ";
}
$sOrder = substr_replace( $sOrder, "", -2 );
}

/* 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

SELECT FIELD NAMES
From table_name

*/
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE field_name LIKE '%".$_GET['sSearch']."%' OR ".
"field_name LIKE '%".$_GET['sSearch']."%' OR ".
"field_name LIKE '%".$_GET['sSearch']."%'";
}


$fil_Query = "SELECT COUNT(*) as filert_info
From table_name
$sWhere
";
$fil_Result = sqlsrv_query($conn,$fil_Query);
$afil_Result = sqlsrv_fetch_array($fil_Result);
$iFilteredTotal = $afil_Result[0];


$sQuery = "SELECT ".$sLimit." FIELD NAMES
From table_name
$sWhere


";
$rResult = sqlsrv_query($conn,$sQuery) ;


$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = sqlsrv_fetch_array( $rResult ) )
{
$sOutput .= "[";
$sOutput .= '"'.$aRow[0].'",';
$sOutput .= '"'.$aRow[1].'",';
$sOutput .= '"'.$aRow[2].'"';
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;


function fnColumnToField( $i )
{
if ( $i == 0 )
return "first_name";
else if ( $i == 1 )
return "last_name";
else if ( $i == 1 )
return "id_number";

}
?>

[/code]

DONE BY JOHN-MATTHEW SINCLAIR AND RYAN GOLDSON - JAMAICA

allan

Nice one - thanks for posting this!

Allan