Help with server processing. PHP/MSSQL and AJAX Reload
Help with server processing. PHP/MSSQL and AJAX Reload
aharro12
Posts: 10Questions: 1Answers: 0
This all works fine when using client side but I have to display this in IE11 and it does not render a couple hundred rows without lagging so I switched to server side.
I change a row in SQL and I can see Valid JSON coming back after the 3 seconds in the header but I cannot get the table to redraw the latest information. What am I doing wrong?
Eg. AMOS12 is highlighted yellow but the JSON shows AMOS122 or whatever I change it to.
Also, If I search just 'AMOS' the JSON returns back with just 1 record like it should but does not update the table either
Main Page
<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=11">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Server Side Processing
</title>
<script src="/js/jquery.min.js"></script>
<script src="/js/jquery.dataTables.min.js"></script>
<script src="/js/dataTables.responsive.min.js"></script>
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="css/responsive.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="css/override.css">
<?php $ZoneID=$ _GET[ "zone"]; ?>
<?php $searchValue=$ _GET[ 'search'][ 'value']; ?>
</head>
<body>
<table id="example" class="display no-wrap" width="100%" cellspacing="0">
<thead>
<tr>
<th>Name
</th>
<th>Surname
</th>
<th>Last Access
</th>
<th>Last Zone
</th>
<th>Phone
</th>
<th>Escort Name
</th>
<th>Escort Surname
</th>
</tr>
</thead>
</table>
<script type="text/javascript">
$(document).ready(function() {
var table = $('#example').DataTable({
"ajax": {
"url": "/data.php",
"data": {
"zone": "<?php echo $ZoneID ?>",
"searchvalue": "<?php echo $searchValue ?>",
}
},
serverSide: true,
"bPaginate": false,
"bProcessing": true,
"ordering": false,
fixedHeader: true,
responsive: true,
"pagingType": "simple_numbers",
"scrollY": 645,
"scrollCollapse": true,
"bSearchable": true,
"pageLength": 1000,
columns: [{
mData: 'Name'
}, {
mData: 'Surname'
}, {
mData: 'Expr1'
}, {
mData: 'LastZoneName'
}, {
mData: 'Telephone'
}, {
mData: 'Expr2'
}, {
mData: 'Expr3'
}],
rowId: 'SbiID',
});
setInterval(function() {
table.ajax.reload(null, false);
}, 3000);
});
</script>
</body>
</html>
My data.php file
<?php $ZoneID = $_GET["zone"]; ?>
<?php $searchValue = $_GET['search']['value']; ?>
<?php include "conn.php";?>
<?php include "query.php";?>
<?php
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
die( print_r( sqlsrv_errors(), true) );
}
$data = array();
while( $rows = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
$data[] = $rows;
}
$results = array(
"sEcho" => 1,
"iTotalRecords" => count($data),
"iTotalDisplayRecords" => count($data),
"aaData" => $data
);
echo json_encode($results);
<?php
>
?>
My query.php file
```
<?php
$ZoneID = $_GET["zone"];
$searchValue = $_GET['search']['value'];
$sql = "";
$sql .= "SELECT CHLocalInformation.SbiID, COALESCE (Employee_1.Surname, Visitor.Surname) AS Surname, ";
$sql .= "COALESCE (Employee_1.Name, Visitor.Name) AS Name, CONVERT(VARCHAR(17), ";
$sql .= "CHLocalInformation.LastAccessDateTime, 113) AS Expr1, CHLocalInformation.LastZoneName, Visitor.Telephone, Employee.Name AS Expr2, Employee.Surname AS Expr3 ";
$sql .= "FROM Visitor INNER JOIN AC_VISIT ON Visitor.SbiID = AC_VISIT.SBI_ID INNER JOIN ";
$sql .= "Employee ON AC_VISIT.REFERENCE_INDEXBOOK = Employee.SbiID RIGHT OUTER JOIN ";
$sql .= "CHLocalInformation LEFT OUTER JOIN Employee AS Employee_1 ON ";
$sql .= "CHLocalInformation.SbiID = Employee_1.SbiID ON AC_VISIT.SBI_ID = CHLocalInformation.SbiID ";
$sql .= "WHERE (CHLocalInformation.LastZoneName LIKE N'" . $ZoneID . "') AND ";
$sql .= "(CHLocalInformation.LastAccessDateTime >= DATEADD(day, - 3000, GETDATE())) AND (NOT (COALESCE (Employee_1.Surname, Visitor.Surname) IS NULL)) ";
$sql .= "AND (COALESCE (Employee_1.Name, Visitor.Name) LIKE N'" . $searchValue . "%')" ;
$sql .= "ORDER BY CHLocalInformation.LastAccessDateTime DESC" ;
This question has an accepted answers - jump to answer
Answers
Hi @aharro12 ,
That's definitely odd. The only thing I can think of is whether there's some special characters in that string, or a different character set. We're happy to take a look, but please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
Hi Colin
Thanks for the reply, Found the issue after some time.
In my PHP page I had
I changed the sEcho value to match the draw value in the GET request
Then sent this back in the JSON results
Was pretty difficult to find and took me about 8 hours in total. Would be pretty handy I think if this was a bit easier to find, I only came across is using google not the dataTables documentation.