PHP with MSSQL

majortom

I have a database in SQL Server , and I'm trying to populate a datatable with it. I'm using the code from here:

http://www.datatables.net/development/server-side/php_mssql_odbc

However I get this error in a popup with the page loads

[quote]DataTables warning (table id = 'table1'): DataTables warning: JSON data from server culd not be parsed. This is caused by a JSON formatting error.[/quote]

When I navigate to the .php after I load the page, I get this error:

[quote]SELECT TOP 10 FROM tablename WHERE NOT IN ( SELECT FROM ( SELECT TOP 0 FROM tablename ) as [virtTable] ) : Array[/quote]

Here is my code in the .php file

[code]
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "";

/* DB table to use */
$sTable = "PriClass";

/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "HG_DMS";
$gaSql['server'] = "SERVERNAME";

/*
* Columns
* If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
* If not this will grab all the columns associated with $sTable
*/
$aColumns = array();

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* ODBC connection
*/
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
$params = array();
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );


/* Ordering */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
$row = array();
for ( $i=0 ; $i
[/code]

Thanks

majortom

I found that this code outputs JSON properly, but it still does not work. The popup doesn't appear anymore, but the, "Processing" box is still stuck over the grid. Also, none of the cell are populated.

http://www.joshkrajnak.com/?p=1714

[code]
<?php
// This is for SQL Authentication. I've added instructions if you are using Windows Authentication

// Uncomment this line for troubleshooting / if nothing displays
//ini_set('display_errors', 'On');

// Server Name
$myServer = "SRVR";

// If using Windows Authentication, delete this line and the $myPass line as well.
// SQL Server User that has permission to the database
$myUser = "usr";

// SQL Server User Password
$myPass = "Passwd1";

// Database
$myDB = "TestDB";

// If using Windows Authentication, get rid of, "'UID'=>$myUser, 'PWD'=>$myPass, "
// Notice that the latest driver uses sqlsrv rather than mssql
$conn = sqlsrv_connect($myServer, array('UID'=>$myUser, 'PWD'=>$myPass, 'Database'=>$myDB));

// Change TestDB.vwTestData to YOURDB.dbo.YOURTABLENAME
$sql ="SELECT * FROM TestDB.dbo.vwTestData";
$data = sqlsrv_query($conn, $sql);

$result = array();

do {
while ($row = sqlsrv_fetch_array($data, SQLSRV_FETCH_ASSOC)){
$result[] = $row;
}
}while ( sqlsrv_next_result($data) );

// This will output in JSON format if you try to hit the page in a browser
echo json_encode($result);

sqlsrv_free_stmt($data);
sqlsrv_close($conn);
?>
[/code]

The .js code

[code]
$(document).ready(function() {
var table = $('#table1').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "scripts/script.php",

"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"sSwfPath": "/swf/copy_csv_xls_pdf.swf"
}
} );
});
[/code]

Anyone have any idea of how to fix this? I'm also going to need filtering, sorting and pagenation. Do I have to include this in the .php file?

majortom

So I solved the problem (and by I, I mean Allan solved the problem). I didn't really need server-side processing, so I used the .php code that outputting the raw JSON, and modified my initialization code.

scripts/script.php

[code]
<?php
// This is for SQL Authentication. I've added instructions if you are using Windows Authentication

// Uncomment this line for troubleshooting / if nothing displays
//ini_set('display_errors', 'On');

// Server Name
$myServer = "SRVR";

// If using Windows Authentication, delete this line and the $myPass line as well.
// SQL Server User that has permission to the database
$myUser = "usr";

// SQL Server User Password
$myPass = "Passwd1";

// Database
$myDB = "TestDB";

// If using Windows Authentication, get rid of, "'UID'=>$myUser, 'PWD'=>$myPass, "
// Notice that the latest driver uses sqlsrv rather than mssql
$conn = sqlsrv_connect($myServer, array('UID'=>$myUser, 'PWD'=>$myPass, 'Database'=>$myDB));

// Change TestDB.vwTestData to YOURDB.dbo.YOURTABLENAME
$sql ="SELECT * FROM TestDB.dbo.vwTestData";
$data = sqlsrv_query($conn, $sql);

$result = array();

do {
while ($row = sqlsrv_fetch_array($data, SQLSRV_FETCH_ASSOC)){
$result[] = $row;
}
}while ( sqlsrv_next_result($data) );

// This will output in JSON format if you try to hit the page in a browser
echo json_encode($result);

sqlsrv_free_stmt($data);
sqlsrv_close($conn);
?>
[/code]

The .js

[code]
$(document).ready(function() {
$('#table1').dataTable( {
"bProcessing": true,
"sAjaxSource": "scripts/script.php",
"sAjaxDataProp": "",
"aoColumns": [
{ "mData": "Column 1" },
{ "mData": "Column 2" },
{ "mData": "etc..." },
]
} );
});
[/code]

.html

[code]



Column 1
Column 2
etc...




Loading data from server




Column 1
Column 2
etc...



[/code]

allan

Phew! Good to hear you got it going in the end!

Regards,
Allan

smd

Thank you! This helped me immensely!!!

This product is wonderful.

smd

How would we get pagination to work with this script?

majortom

It should work if you use this code. At least it does for me

Try setting

[code]"sPaginationType": "full_numbers"[/code]

in your initialization script

smd

[quote]majortom said: ls[/quote]

Thanks for the feedback, I've tried that and it causes my data to not load....here is my init script.

[code]$(document).ready(function() {
$('#Test').dataTable( {
"bProcessing": true,
"sPaginationType": "full_numbers"
"sAjaxSource": "/dt/examples/server_side/scripts/script.php",
"sAjaxDataProp": "",
"aoColumns": [
{ "mData": "SenderId" },
{ "mData": "RecieverId" }
]
} );[/code]

majortom

You're missing a comma after "sPaginationType": "full_numbers"

smd

duh...thanks for your help.

smd

Any ideas on how to get this option with server-side processing, every attempt I've made has failed. I'll have roughly 15-20 million rows of data at any given time and the browsers can't handle it. Any help would be appreciated.

Thanks,

majortom

No, sorry I don't. That's what I initially was trying to do, but I only had a couple thousand rows so it wasn't a big deal. Allan might be able to help you though

sbrown777

What about PHP that uses FreeTDS?

allan

FreeTDS as in: http://www.freetds.org/ ? I'm not aware of anyone having written a server-side processing script which uses that library, but it looks like it should be quite possible!

Allan