Server-side processing Datatables pagination Microsoft SQL

0
votes

Im trying to setup pagination with datatables using PHP PDO with MS SQL, since "limit" is not applicable i find it really hard to make the code work.

I have tried the "TOP" syntax but it will only filter the specified number and the pagination wont work.

I have tried offset and fetch still not working.

These is the working code when Mysql is used and its so easy to understand and perform.

if($_POST["length"] != -1)
   {
$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
   }

These are the codes i tried (sorry im not really great in coding) :

//if($_POST["length"] != -1)
   {
Trial 1 : //$query .= "TOP " . $_POST['start'] . " OFFSET " .                     $_POST['length'];


Trial 2 : //$query .= "SELECT * from item ORDER BY id DESC offset 0 rows fetch next 10 rows only ";


    Trial 3 ://$query .="AND id BETWEEN ".intval( $_POST["start"] )." AND ".intval( $_POST["length"] );" "

}

The result should be a pagination with 10 filtered records each.enter image description here

Update 1:

Here is the screenshot of the query i tried to test it in a MS SQL server but getting an error (using MS SQL 2008)

SQL Query

2
phpjquerysqlsql-serverdatatables

2 Answers

0
votes

Your approach depends on SQL Server version.

Approach, based on ORDER BY clause with OFFSET and FETCH as a paging solution requires SQL Server 2012+. Your syntax seems correct, so next code should work:

<?php
if ($_POST["length"] != -1) {
    $query = "
        SELECT * 
        FROM item 
        ORDER BY id DESC OFFSET ".($_POST['start']-1)." ROWS FETCH NEXT ".$_POST["length"]." ROWS ONLY
    ";
}
?>

For SQL Server 2008+, you may use ROW_NUMBER() as a paging solution:

<?php
if ($_POST["length"] != -1) {
    $query = 
        "SELECT *
        FROM (
            SELECT 
                *, 
                ROW_NUMBER() OVER (ORDER BY id DESC) AS Rn
            FROM item
        )
        WHERE Rn BETWEEN ".$_POST['start']." AND ".($_POST['start'] + $_POST['length'] - 1);        
}
?>
0
votes

In MSSQL to use the limit you will need to write your query like this:

ORDER BY X.Field
OFFSET 20 ROWS 
FETCH NEXT 10 ROW ONLY OPTION (RECOMPILE)

And this will skip the first 20 records and fetch the next 10.