PHP SQL Server Alternative for TOP and LIMIT of MY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PHP SQL Server Alternative for TOP and LIMIT of MY

Author

Topic

bracers
Starting Member

1 Post

Posted - 2015-03-15 : 15:10:41

Hi Guys, 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.

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

It does work somehow when I type in:

$construct1 ="SELECT TOP $per_page * FROM ENSEMBLE WHERE $construct";

but when I add $start:

$construct1 ="SELECT TOP $start, $per_page * FROM ENSEMBLE WHERE $construct";

It gives me an error message saying..

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\trueensemble\search1.php on line 51

I decided running error reporting after that to get a detailed error and I got:

[SQL Server]Incorrect syntax near ','. )

I tried removing comma, now gives me an error message:

[SQL Server]Incorrect syntax near the keyword 'FROM'. )

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-15 : 17:30:16

THe correct syntax for TOP is


TOP (expression) [PERCENT]
    [ WITH TIES ]

from https://msdn.microsoft.com/en-us/library/ms189463.aspx

Your query is saying "return the top $start number of rows and the value $per_page * the column named 'FROM'

Perhaps you want:

select top($start) * FROM ....

or select *
from ...
order by ...
offset $start
fetch $per_page rows

see this: https://msdn.microsoft.com/en-us/library/ms188385.aspx

Note that if you use TOP without an ORDER BY clause, the order of the rows returned is not guaranteed in any way. Consider the ordering to be random.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources