DataTables with php and MsSql
DataTables with php and MsSql
Hello,
I tried the server-side processing example with MySql database and works fine, but I'd like to use it with MsSql.
I created an MsSql database and a same table structure and content like in MySql, and I modified the server_processing.php to use the MsSql database as data source.
It seems works, I can see the table header and footer with the correct coulmn names, but the table body says that 'Loading data from server'.
If I change "sAjaxSource": "mssql_server_processing.php" back to "sAjaxSource": "mysql_server_processing.php" it works again.
I checked the $sOutput ouf the two script but both of them are equal and valid.
You can find the MsSql server_processing.php here: http://pastebin.com/udhXB04G
I removed the version column from both script, and I didn't use any limit, filtering and sorting option in the Sql queries, I just wanted to check that is works with MsSql.
Do you have any idea why I can't see any data in the table body?
Thank you in advance.
Norbert
I tried the server-side processing example with MySql database and works fine, but I'd like to use it with MsSql.
I created an MsSql database and a same table structure and content like in MySql, and I modified the server_processing.php to use the MsSql database as data source.
It seems works, I can see the table header and footer with the correct coulmn names, but the table body says that 'Loading data from server'.
If I change "sAjaxSource": "mssql_server_processing.php" back to "sAjaxSource": "mysql_server_processing.php" it works again.
I checked the $sOutput ouf the two script but both of them are equal and valid.
You can find the MsSql server_processing.php here: http://pastebin.com/udhXB04G
I removed the version column from both script, and I didn't use any limit, filtering and sorting option in the Sql queries, I just wanted to check that is works with MsSql.
Do you have any idea why I can't see any data in the table body?
Thank you in advance.
Norbert
This discussion has been closed.
Replies
I am facing exactly the same problem. Was trying to solve it for like 2 days!
Please, I am sure, somebody used it with MSSQL. Share the solution.
If anyone could post a sample of the code with MSSQL, we would really appreciate it.
[code]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
DataTables example
var oTable;
/* Formating function for row details */
function fnFormatDetails ( nTr )
{
var aData = oTable.fnGetData( nTr );
var sOut = '';
sOut += 'Rendering engine:'+aData[2]+' '+aData[5]+'';
sOut += 'Link to source:Could provide a link here';
sOut += 'Extra info:And any further details here (images etc)';
sOut += '';
return sOut;
}
/* Event handler function */
function fnOpenClose ( oSettings )
{
$('td img', oTable.fnGetNodes() ).each( function () {
$(this).click( function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "../../../../images/grid_images/details_open.png";
/* fnClose doesn't do anything for server-side processing - do it ourselves :-) */
var nRemove = $(nTr).next()[0];
nRemove.parentNode.removeChild( nRemove );
}
else
{
/* Open this row */
this.src = "../../../../images/grid_images/details_close.png";
oTable.fnOpen( nTr, fnFormatDetails(nTr), 'details' );
}
} );
} );
}
$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing_details_col.php",
"aoColumns": [
{ "sClass": "center", "bSortable": false },
null,
null,
null,
null,
{ "sClass": "center" },
{ "sClass": "center" }
],
"aaSorting": [[1, 'asc']],
"fnDrawCallback": fnOpenClose
} );
} );
TEST
Student ID
First Name
Last Name
Grade
Student Name
Status
Loading data from server
[/code]
[code]
<?php
$aColumns = array( 'StudentID', 'FirstName_1', 'FamilyName_1', 'ClassName_1', 'UserName', 'FamilyID', 'FatherName_1', 'MotherName_1',
'FatherMobile', 'MotherMobile', 'EmailUserID', 'StudentSystemStatusName_1' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "StudentID";
/* Database connection information */
$gaSql['user'] = "sa";
$gaSql['password'] = "";
$gaSql['db'] = "iCampusDevelopment";
$gaSql['server'] = "192.168.1.153";
if(isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] != '-1')
$limit = $_GET['iDisplayLength'];
else
$limit = 50;
if(isset($_GET['iDisplayStart']))
$top = $_GET['iDisplayStart'];
else
$top = 0;
$gaSql['link'] = mssql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] );
mssql_select_db( $gaSql['db'], $gaSql['link'] );
/* mysql_real_escape_string */
function mssql_escape($data)
{
if(is_numeric($data))
return $data;
$unpacked = unpack('H*hex', $data);
return '0x' . $unpacked['hex'];
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]
[code]{"sEcho": 0, "iTotalRecords": 655, "iTotalDisplayRecords": 655, "aaData": [ ["","101","Yousuf1500","AlQassem","101","1-B","Ditributed"],["","102","Mohammed","AlQassem","mohammed.alqassem","5-A","Ditributed"],["","103","Sara ","AlQassem","Sara.AlQassem","7-B","Ditributed"],["","201","Sana","Ziyaeen","Sana.Ziyaeen","N/A","Ditributed"], [...] etc.[/code]The first field in the above example is not empty as it seems, but is actually an image.
But the html page displays this:
No matching records found
Showing 1 to 0 of 655 entries
Valid JSON
Try to drop out this ""bServerSide": true," from the html file.
cheers
The "sEcho": 0, looks wrong to me. It should never be zero, but rather start at 1 and increase on every draw. If you are able to give us a link to your page that would be very handy.
Allan
"sEcho": 0, "iTotalRecords": 133224, "iTotalDisplayRecords": 133224, "aaData": [ ......
sEcho = 0 with this code, one can help us ?