MSSQL/PHP Server Side Processing Example

theflarenet

I've spent countless hours searching for a working Server-side Processing example in PHP & MSSQL (SQL Server 2008) instead of MySQL but could not find one. The closest one found: https://datatables.net/forums/discussion/32123/server-side-processing-with-pdo-mssql

I am unsure of how it should be configured and lost hope in finding a solution. I hope someone out there has a method!

tangerine

Why can't you use the example you have linked to?

theflarenet

There's no mention to how you initiate the jQuery piece correctly with the PHP code provided in that link. What I have so far is:

$('#testingTable').DataTable({
            "bSortClasses": false,
            "processing": true,
            "paging": false,
            "serverSide": true,
            "ajax": "ssp.php"
});

allan

The example in the thread you linked to looks okay. The client-side initialisation for server-side processing will be the same regardless of what is used on the server-side. What you have looks okay, except setting paging: false will negate any benefit that you get from server-side processing. The only thing it will do is add latency into your application. This is because you are always downloading the full data set on every draw.

Allan

theflarenet

Allan, thank you for that quick response. I didn't realize I had paging set on false so I set it back to true; however, I'm still unable connect to the server-side processing (ssp.php) in my case which I believe is done correctly. Loading up my application displays a javascript alert error:

DataTables warning: table id=testingTable - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

I have done so to debug this Ajax error and only found that the GET request is: /ssp.php?draw=1&columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=10&search[value]=&search[regex]=false&_=1501007903403

I'm not sure what's configured incorrectly to link up the row/column. Am I supposed to actually edit the $columns array within my ssp.php?

$columns = array(
array( 'db' => 'col_1', 'dt' => 'A' ),
array( 'db' => 'col_2', 'dt' => 'B' ),
array( 'db' => 'col_3', 'dt' => 'C' ),
array( 'db' => 'col_4', 'dt' => 'D' ),
array( 'db' => 'col_5', 'dt' => 'E' ),
array( 'db' => 'col_6', 'dt' => 'F' ),
array( 'db' => 'col_7', 'dt' => 'G' ),
array( 'db' => 'col_8', 'dt' => 'H' )
);

Oddly enough, I thought it was dynamic and it would automatically pull the column names. My apologies if I am wrong. I am currently testing with a table (which is correctly fetched by ssp.php) that has one column called prime_id that goes up to 1000. Please let me know... I'm so glad I've made it this far.

tangerine

Am I supposed to actually edit the $columns array within my ssp.php?

Yes! The $columns array should precisely reflect the columns you are working with in your own db schema.

theflarenet

Thank you tangerine.

I think I have set it up correctly to output one column for my table but it's processing all 1000+ records at once instead of using pagination. I cannot tell what's set incorrectly.

My HTML:

<table id='testingTable' border='1' cellpadding='3' cellspacing='2'>
<thead><tr>
<th>Key_ID</th>
</tr></thead><tbody>
<tr><td><font size='1'>[test]</font></td></tr>
</tbody></table>

JQuery:

$('#testingTable').DataTable({
            "bSortClasses": false,
            "processing": true,
            "paging": true,
            "pageLength": 10,
            "serverSide": true,
            "ajax": "ssp.php"
});

PHP:

$sql_details = array(
    'db'   => 'db9',
    'host' => 'testinghost'
);
$table = 'db9.testingTable';
$primaryKey = 'key_id';
$columns = array(
    array( 'db' => 'Key_ID', 'dt' => '0' )  // Only column with 1000+ records for testing. I only want to output this.
);

tangerine

Does your server-side code have this:

echo json_encode(SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns ));

?

theflarenet

Yes. Sorry, I did not include the whole PHP server-side code.

echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )
);

It populates my table but with all the records (Key_ID) that exist without pagination. I see all records displayed upfront.

allan

You are using $_POST but the Ajax request is being sent as a GET. Change your PHP script to use $_GET and it should work.

Allan

theflarenet

Thank you, allan. I've come a step closer to the solution and discovered that SSP::simple will not work for me since I'm stuck on SQL Server 2008 rather than 2012 which has "OFFSET" arguments. Having that said, I resorted to another solution which apparently works under SQL Server 2008: https://m.datatables.net/forums/discussion/21009/problems-with-server-side-processing-and-sql-server-express

I've tried the same exact code provided by this user and found that this is the CLOSEST working example posted online for PHP / SQLSRV 2008 server-side processing. It does indeed have pagination however traversing through the pages do not change the data. The data is not updated at all and I'm unsure how to troubleshoot this as I'm looking at the recordsTotal and recordsFiltered pieces in the code. I'm absolutely exhausted and cannot figure this out! I hope someone out there has the ability to find what the culprit is here.

allan

Probably because that script is expecting legacy parameters (see the server-side processing manual).

Allan