PHP with MSSQL
PHP with MSSQL
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
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
This discussion has been closed.
Replies
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?
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]
Regards,
Allan
This product is wonderful.
Try setting
[code]"sPaginationType": "full_numbers"[/code]
in your initialization script
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]
Thanks,
Allan