PHP SQL Server Alternative for TOP and LIMIT of MYSQL

Question

I'm trying to figure out on how can i translate the codes I've created in php mysql to php sqlsrv.

Basically, I used LIMIT in MySQL to limit the pagination. However, this is not supported in SQL Server and I'm really having a hard time like 3 months already to figure this out.

Below is the MySQL Code I wanted to translate on SQLSRV:

$start="";
$per_page = 1;
$start = $_GET['start'];
$max_pages = ($foundnum / $per_page);
if(!$start)
$start=0; 
$getquery = mysql_query("SELECT * FROM knowledgebase WHERE $construct LIMIT $start, $per_page");

And below code is what I currently have issues running in SQLSRV:

$start="";
$per_page = 1;
$start = $_GET['start'];
$max_pages = ($foundnum / $per_page);
if(!$start)
$start=0; 
$construct1 ="SELECT * FROM ENSEMBLE WHERE $construct";
$run1=sqlsrv_query($con,$construct1, array(), array('scrollable' => 'keyset'));

I can easily add LIMIT in the line of code $construct1 ="SELECT * FROM ENSEMBLE WHERE $construct"; but I didn't do it as it is not possible anyway.

Anyone can kindly help me out on how I can possible translate or rewrite it? TIA.

score 2 · Accepted Answer · edited May 23 '17 at 12:22

2

Look at these posts. Simulating LIMIT from MySQL is little bit tricky.

LIMIT 10..20 in SQL Server

How to implement LIMIT with Microsoft SQL Server?

From SQL server 2012 there is OFFSET and FETCH.

SELECT * FROM Table ORDER BY FirstName OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

It should be equivalent to LIMIT 10, 5.

edited May 23 '17 at 12:22

Community

1
1

answered Mar 15 '15 at 20:00

kba

3,880
2
13
24

Thanks! It worked. But there seems to be a problem when i try to apply the variable for paginating a web page, just like what I did in mysql. I dont have any idea on how can I achieve the same goal I used: $getquery = mysql_query("SELECT * FROM knowledgebase WHERE $construct LIMIT $start, $per_page");. I applied it in the information you have given melike this: $construct1 ="SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as row FROM ENSEMBLE ) a WHERE row > $start and row <= 10" ; – user3596261 Mar 15 '15 at 20:24
Well if I understand your code well it looks like `SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as row FROM ENSEMBLE ) a WHERE row > $start AND row <= $start + $per_page`. Rows are numbered from 1 to n. Instead LIMIT 10, 5 (for example), you need rows from number 11 to number 15. Remember, unlike mysql, rows are numbered from 1 instead 0 like with LIMIT. – kba Mar 15 '15 at 21:05
Thank you so much for helping me resolved it, + reminding me that rows in sqlsrv starts with 1. Appreciate it alot. – user3596261 Mar 15 '15 at 21:09
I am glad to help you. You can accept my answer as solution of you problem. – kba Mar 17 '15 at 15:24

David Soussan · Answer 2 · 2015-03-15T19:58:44.203

1

TOP is the equivalent of LIMIT in sql server: http://www.w3schools.com/sql/sql_top.asp

As has been pointed out below, TOP is not the equivalent realy. In fact you need to use OFFSET and FETCH to exactly match the LIMIT behaviour: https://msdn.microsoft.com/en-us/library/ms188385.aspx

edited Mar 15 '15 at 19:58

answered Mar 15 '15 at 18:10

David Soussan

2,562
1
14
19

If you want to ask for further help, do not edit my answer but add a coment below it, or ask another question. In this case, ask another question as that is another problem. – David Soussan Mar 15 '15 at 18:38
TOP is not equivalent to MySQL limit because it does not support offset. You can not easily make a pagination with only TOP clause. – kba Mar 15 '15 at 19:54

PHP SQL Server Alternative for TOP and LIMIT of MYSQL

2 Answers2

Linked