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)