MSSQL Filtering - HELP FOR ME = HELP FOR EVERYONE !
MSSQL Filtering - HELP FOR ME = HELP FOR EVERYONE !
I though i'd post my script as i don't think it's covered or if it is it's not clear
I'm using this script to connect to a MSSQL server using php and a sqlsrv driver which almost works. Here's my server_processing script:
I can successfully filter by both the between date and the user drop down list however there are a few problems i cannot understand:
Current Set-up
I have 1,436 entries, when the problems below occur it does show the filtered from correctly and showing x to x is always showing as expected. and the page numbers increase/decrease as expect it just fails to reload the data.
(Sorry about the BIG POST) but this is really important and i think as it contains MSSQL for PHP i think it may be useful for other users aswell.
Show Entries = 10, Users=NULL, fDate=, tDate=
Problem: When moving from page 1 to page 2 the data does not change. onto page 11 and moving to/from page 12 again nothing changes but between pages 2and 10 it functions as expected
Trail 2:
Show Entries = 25, Users=NULL, fDate=, tDate=
Problem: Leaving page 4 going onto page 5 and anything above has no effect the data in the tables stays the same. (nothing changes)
Trail 3:
Show Entries = 10, Users=0, fDate=, tDate=
Problem: No pages work as expected
Trail 4:
Show Entries = 25, Users=0, fDate=, tDate=
Problem: no pages work as expect, however the data is NOT duplicate the other data is valid.
Trail 5:
Show Entries = 10, Users=NULL, fDate=2011-11-01, tDate=2011-11-16
Problem: First few pages function as expected up to page 11 where it stops refreshing.
Trail 6:
Show Entries = 25, Users=0, fDate=2011-11-01, tDate=2011-11-16 AND Search Box Contains "CP" (all entries contain "CP")
Problem: nothing loads.
[code]
<?php
$fromdate=$_GET['fDate'];
$todate=$_GET['tDate'];
$aColumns = array( 'userid', 'timestmp', 'page');
$sIndexColumn = "id";
$sTable = "CPLogs";
$gaSql['user'] = "***";
$gaSql['password'] = "***";
$gaSql['db'] = "***";
$gaSql['server'] = "***";
$gaSql['wuser'] = "***";
$gaSql['wpassword'] = "***";
$gaSql['wdb'] = "***";
$gaSql['wserver'] = "***";
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], array('Database'=>$gaSql['db'] ,'UID'=>$gaSql['user'], 'PWD'=>$gaSql['password'] )) or
die( "Connection failed: " . var_dump(sqlsrv_errors()) );
$gaSql['warehouse'] = sqlsrv_connect( $gaSql['wserver'], array('Database'=>$gaSql['wdb'] ,'UID'=>$gaSql['wuser'], 'PWD'=>$gaSql['wpassword'] )) or
die( "Connection failed: " . var_dump(sqlsrv_errors()) );
/* I'VE NO IDEA WHAT THIS DOES?
$cSql = "SELECT colname FROM $sTable";
$cResult = sqlsrv_query( $gaSql['link'], $cSql ) or die("FAIL:".var_dump(sqlsrv_errors()));
while ( $r = sqlsrv_fetch_array( $cResult ) ) {
array_push( $aColumns, $r['colname'] );
};*/
$sLimit = "";
$sLimit = "TOP " . addslashes( $_GET['iDisplayLength'] ) . " ";
$sLimit2 = "";
$sLimit2 = "TOP " . addslashes( ((int)$_GET['iDisplayStart'] + (int)$_GET['iDisplayLength']) ) . " ";
$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 ; $iaccount_name;
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>[/code]
I'm using this script to connect to a MSSQL server using php and a sqlsrv driver which almost works. Here's my server_processing script:
I can successfully filter by both the between date and the user drop down list however there are a few problems i cannot understand:
Current Set-up
I have 1,436 entries, when the problems below occur it does show the filtered from correctly and showing x to x is always showing as expected. and the page numbers increase/decrease as expect it just fails to reload the data.
(Sorry about the BIG POST) but this is really important and i think as it contains MSSQL for PHP i think it may be useful for other users aswell.
Show Entries = 10, Users=NULL, fDate=, tDate=
Problem: When moving from page 1 to page 2 the data does not change. onto page 11 and moving to/from page 12 again nothing changes but between pages 2and 10 it functions as expected
Trail 2:
Show Entries = 25, Users=NULL, fDate=, tDate=
Problem: Leaving page 4 going onto page 5 and anything above has no effect the data in the tables stays the same. (nothing changes)
Trail 3:
Show Entries = 10, Users=0, fDate=, tDate=
Problem: No pages work as expected
Trail 4:
Show Entries = 25, Users=0, fDate=, tDate=
Problem: no pages work as expect, however the data is NOT duplicate the other data is valid.
Trail 5:
Show Entries = 10, Users=NULL, fDate=2011-11-01, tDate=2011-11-16
Problem: First few pages function as expected up to page 11 where it stops refreshing.
Trail 6:
Show Entries = 25, Users=0, fDate=2011-11-01, tDate=2011-11-16 AND Search Box Contains "CP" (all entries contain "CP")
Problem: nothing loads.
[code]
<?php
$fromdate=$_GET['fDate'];
$todate=$_GET['tDate'];
$aColumns = array( 'userid', 'timestmp', 'page');
$sIndexColumn = "id";
$sTable = "CPLogs";
$gaSql['user'] = "***";
$gaSql['password'] = "***";
$gaSql['db'] = "***";
$gaSql['server'] = "***";
$gaSql['wuser'] = "***";
$gaSql['wpassword'] = "***";
$gaSql['wdb'] = "***";
$gaSql['wserver'] = "***";
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], array('Database'=>$gaSql['db'] ,'UID'=>$gaSql['user'], 'PWD'=>$gaSql['password'] )) or
die( "Connection failed: " . var_dump(sqlsrv_errors()) );
$gaSql['warehouse'] = sqlsrv_connect( $gaSql['wserver'], array('Database'=>$gaSql['wdb'] ,'UID'=>$gaSql['wuser'], 'PWD'=>$gaSql['wpassword'] )) or
die( "Connection failed: " . var_dump(sqlsrv_errors()) );
/* I'VE NO IDEA WHAT THIS DOES?
$cSql = "SELECT colname FROM $sTable";
$cResult = sqlsrv_query( $gaSql['link'], $cSql ) or die("FAIL:".var_dump(sqlsrv_errors()));
while ( $r = sqlsrv_fetch_array( $cResult ) ) {
array_push( $aColumns, $r['colname'] );
};*/
$sLimit = "";
$sLimit = "TOP " . addslashes( $_GET['iDisplayLength'] ) . " ";
$sLimit2 = "";
$sLimit2 = "TOP " . addslashes( ((int)$_GET['iDisplayStart'] + (int)$_GET['iDisplayLength']) ) . " ";
$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 ; $iaccount_name;
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>[/code]
This discussion has been closed.
Replies
[code]
<!DOCTYPE html>
$(function() {
$( ".datepicker" ).datepicker({ dateFormat: 'yy-mm-dd' });
});
$(document).ready(function() {
$('#Details').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "http://osprey/cp/datatables/getLogs.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* ... additional variables ... */
aoData.push({"name":"fDate","value": $('#from_date').val()});
aoData.push({"name":"tDate","value": $('#to_date').val()});
aoData.push({"name":"usr","value": $('#users').val()});
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
}
});
} );
View Logs
Date From: No FilterOption XOption XOption XOption XOption XOption XOption XOption XOption XUsers
Date To: Search
Username:
Date/Time
Page URL
Date/Time
Page URL
[/code]
(I've cleared the database)
and just now i noticed when scrolling through the pages it looks like it's scrolling however it's not showing all the pages yet after sorting (clicking on a column) say i click on column one it seems to jam column one yet show different entries relating to column ones value. Yet when viewing50 records at a time it shows ALL the records as it should ! (I have 36 entries) If you show 25 entries same problem happens as when showing 10 rows.