MSSQL and PHP : query as table
MSSQL and PHP : query as table
I've decided to post my code since it took me days to get this working properly.
Maybe someone can use this.
I'm not using ODBC to connect to the sqlsrv (which is a MS SQL server 2005 I think) but the php mssql extension.
Once I make a complete function out of this in which you can provide
- the query as a base table
- the rows returned as array
I'll post it here.
Furthermore : dataTables is nice but the documentation often lacks depth.
The paging query shown on the MSSQL ODBC page is simply wrong.
I believe the OVER sql-function should be used to get the paging done, but thus far I cannot get it to work like that.
[code]$sQuery= "SELECT ".implode(",",$aColumns)." FROM (SELECT row_number() OVER (ORDER BY ".$sIndexColumn.") AS rownum, ".implode(",",$aColumns)." FROM $sTable $sWhere $sOrder ) AS A WHERE A.rownum BETWEEN ".$top." AND ".($top+$limit);[/code] hence I commented it and used hamidych's function.
[code]<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "site_id";
/* DB table to use */
//$sTable = "site";
$sTable= " ( SELECT site_id ,(site_id + ' - ' + ISNULL(descr,'') + ISNULL(city,'') + ' (' + country_id + ')') as descr ,country_id from site WHERE country_id IN ('BEL','FRA') ) as tab ";
/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "";
$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 );
/* Columns */
$aColumns = array( 'site_id', 'descr', 'country_id');
/*
$aColumns = array();
$cSql = "SELECT sys.columns.name colname FROM sys.tables JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id WHERE sys.tables.name = '" . $sTable . "'";
$cResult = sqlsrv_query($gaSql['link'], $cSql );
while ( $r = sqlsrv_fetch_array( $cResult ) ) {
array_push( $aColumns, $r['colname'] );
};
*/
/* 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]
Maybe someone can use this.
I'm not using ODBC to connect to the sqlsrv (which is a MS SQL server 2005 I think) but the php mssql extension.
Once I make a complete function out of this in which you can provide
- the query as a base table
- the rows returned as array
I'll post it here.
Furthermore : dataTables is nice but the documentation often lacks depth.
The paging query shown on the MSSQL ODBC page is simply wrong.
I believe the OVER sql-function should be used to get the paging done, but thus far I cannot get it to work like that.
[code]$sQuery= "SELECT ".implode(",",$aColumns)." FROM (SELECT row_number() OVER (ORDER BY ".$sIndexColumn.") AS rownum, ".implode(",",$aColumns)." FROM $sTable $sWhere $sOrder ) AS A WHERE A.rownum BETWEEN ".$top." AND ".($top+$limit);[/code] hence I commented it and used hamidych's function.
[code]<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "site_id";
/* DB table to use */
//$sTable = "site";
$sTable= " ( SELECT site_id ,(site_id + ' - ' + ISNULL(descr,'') + ISNULL(city,'') + ' (' + country_id + ')') as descr ,country_id from site WHERE country_id IN ('BEL','FRA') ) as tab ";
/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "";
$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 );
/* Columns */
$aColumns = array( 'site_id', 'descr', 'country_id');
/*
$aColumns = array();
$cSql = "SELECT sys.columns.name colname FROM sys.tables JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id WHERE sys.tables.name = '" . $sTable . "'";
$cResult = sqlsrv_query($gaSql['link'], $cSql );
while ( $r = sqlsrv_fetch_array( $cResult ) ) {
array_push( $aColumns, $r['colname'] );
};
*/
/* 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]
This discussion has been closed.
Replies
Thanks very much for posting your code. I've just updated the server-side code that you noted was in error. I don't have setup to test it myself, so I think I've got it correct, but it would be great if you would be willing to pass your eye over it?
> dataTables is nice but the documentation often lacks depth.
Yup - I'm going to try and address that as part of the 1.10 work I'm going to do. It just takes time sadly :-(.
Thanks,
Allan
I'll try to investigate the examples more thouroughly tomorrow.
I could add some info on how I set up the php-mssql connection decently (which is also rather hard to google).
tbh, what I seriously miss is a way to simply turn on a debugging mode for the server-side scripts ... something that logs all generated queries, vars, post & get vars somewhere.
Often errors point you in a direction concerning the $_GET vars not being parsed decently, while it purely is the way the queries are built.
A good idea is to set the $_GET vars to a standard value, so you can run the page separately without just seeing a bunch of undefined vars, like I did :
[code]$top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
$limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;[/code]
I have a new PHP database abstraction library for Editor which I will be looking at writing a server-side processing class for, and then open sourcing that. It will provide a lot more OO orientated approach to PHP and server-side processing for DataTables.
Regards,
Allan
If it works out, I'm sure my company will provide me with the funds to buy licences for all the extras and plugins.
I'll keep working on the mssql with php extension scripts and keep posting them.
I'm sure it will contain things you'll be able to use.
Kind Regards,
Tim
Regards,
Allan
Your source is very good but how to convert charset? Right now i wanna to convert utf-8 to tis-620. I tyr to added meta tag and iconv but it's not work.