I have several tables that I am using for reports on a website that I built. I have started using DataTables and am trying to convert to using server side processing. I followed the example here and modified it to use what I already have. Here is the completed file(ServerSide.php):
<?php
$Page = '';
if (isset($_GET['PageName']))
{
//echo "<br>Page = Get<br>";
$Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
//echo "<br>Page = Post<br>";
$Page = $_POST['PageName'];
}
//For testing just this page
//if($Page == '')
//{
// $Page = 'TableHeadings';
//}
include 'DBConn.php';
$headings = array();
$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
$headings[$row] = $rHeadings[$row]["Headings"];
$tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}
foreach($headings as $index => $columnName)
{
$ColumnHeader[] = array('db'=>$columnName,'dt'=>$index);
}
//DB table to use
$table = $Page;
//Table's primary key
$primaryKey = 'id';
//Array of database columns which should be read and sent back to DataTables
$columns = $headings;
//SQL server connection information
$sql_details = array(
'user'=> 'EngsysWebUser',
'pass'=> 'Fr0ntier2016',
'db'=> 'EngSys',
'host'=> 'MAFINFWWAPV01'
);
$connectionInfo = array( "Database"=>$dbname, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect( $servername, $connectionInfo );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
// Get data to display
$Query = "
SELECT count($primaryKey) over() as Row_Count, ".str_replace(" , ", " ", implode(", ", $columns))."
FROM $table";
$rResult = sqlsrv_query( $conn, $Query );
if( $rResult === false) {
die( print_r( sqlsrv_errors(), true) );
}
// Data set length after filtering
$iFilteredTotal = sqlsrv_num_rows( $rResult );
// Total data set length
$sQuery = "SELECT COUNT($primaryKey)
FROM $table";
$rResultTotal = sqlsrv_query( $conn, $sQuery );
$aResultTotal = sqlsrv_fetch_array( $rResultTotal, SQLSRV_FETCH_ASSOC);
$iTotal = $aResultTotal;
// Output
$output = array(
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"Data" => array()
);
while ( $aRow = sqlsrv_fetch_array( $rResult,SQLSRV_FETCH_ASSOC ) )
{
$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] != ' ' )
{
// General output
$row[] = $aRow[ $columns[$i] ];
}
}
$output['Data'][] = $row;
}
echo json_encode($output);
?>
Then I have my other file, which if I'm understanding correctly is supposed to call the previous one(ServerSide.php) and place the json response, formatted as a table, into the tbody of my table(RunningServerSide.php):
<?php
$Page = '';
if (isset($_GET['PageName']))
{
$Page = $_GET['PageName'];
}
elseif (isset($_POST['PageName']))
{
$Page = $_POST['PageName'];
}
include 'DBConn.php';
$headings = array();
$hsql = "select Headings from TableHeadings where TableName = '$Page' order by Id";
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
$headings[$row] = $rHeadings[$row]["Headings"];
$tsqlHeadings = $tsqlHeadings . "[" . $headings[$row] . '],';
}
$Edit = 0;
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
<?php require 'StyleLinks.php'; ?>
<?php include 'DataTableLinks.php'; ?>
</head>
<body>
<table class="NormalTable display nowrap" style="width: 100%; border: 1px" id="DataTable">
<thead>
<tr><?php echo "\n";
if($Edit == 1)
{?>
<th class="cell">Edit</th><?php echo "\n";
}
foreach($headings as $heading)
{?>
<th class="cell"><?php echo $heading; ?></th><?php echo "\n";
}?>
</tr>
</thead><?php echo "\n";?>
</table>
</body>
</html>
Then just for clarity I have included a couple of files in the head of the second file for styling purposes. They include the CSS that I created for tables and the files that I downloaded from DataTables to have their formatting. Plus on the DataTableLinks.php file I have this to initialize the DataTable:
<script>
$(document).ready(function ()
{
$('#DataTable').DataTable(
{
"lengthMenu": [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
"ScrollX": true,
"dom": '<"top"Biflp<"clear">>rt<"bottom"ip<"clear">>',
buttons: [{ extend: 'collection', text: 'Selection', buttons: ['selectAll', 'selectNone'] }, { extend: 'collection', text: 'Export', buttons: ['excel', 'csv', 'pdf']}],
fixedHeader: { header: true, footer: false },
select: true,
"processing": true,
"serverSide": true,
"ajax": { "url": "ServerSide.php", "dataType": "jsonp", "success": function(data){$('#DataTable').append(data);} }
});
});
</script>
I have worked on correcting issues that I found in the console(F12) and there are no longer any problems there. But I still only get the 2 buttons and the table header and then a pop-up that says:
DataTables warning: table id=DataTable - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
The link in that error message says to check the response in the Network portion of the Developer Tools. In there it looks fine to me, but I've never used DataTables before and I've only been working with JSON and AJAX for about a week now. The Response looks like this:
And the Preview tab like this:
