Solved

LIMIT FOR MSSQL

Posted on 2014-03-25
5
259 Views
Last Modified: 2014-04-02
we have this query for search ..

$query = " SELECT * FROM dbo.Rooms WHERE ROOMNO NOT IN (SELECT DISTINCT S_ROOMNO FROM dbo.Stay WHERE ((S_ADATE BETWEEN '$CheckIn' AND '$CheckOut') AND (S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (S_ADATE < '$CheckIn' AND S_DDATE > '$CheckOut') OR (S_ADATE < '$CheckIn' AND ( S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (( S_ADATE BETWEEN '$CheckIn' AND '$CheckOut' ) AND S_DDATE > '$CheckOut')) ";


after fetch result we need to  add this pagination.


$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset-1) + $rowsperpage;
$query .= "LIMIT $offset , $limit";


but this LIMIT is not working with MSSQL Server , it works for Mysql  but i don't know how to do it in MSSQL to  work with  PHP.

Any help is greatly appreciated!!

Tony
0
Comment
Question by:tonyhhisc
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
gozoliet earned 500 total points
ID: 39955015
In SQL 2012 you can use fetch/offset
SELECT email FROM emailTable 
WHERE id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Open in new window


Previous versions would include row_number() over a sort order and then select rows from that table where the row number is between your page values.
$query = " SELECT *, row_number() over ( orDer by roomno) as rownum
 FROM dbo.Rooms WHERE ROOMNO NOT IN (SELECT DISTINCT S_ROOMNO FROM dbo.Stay WHERE ((S_ADATE BETWEEN '$CheckIn' AND '$CheckOut') AND (S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (S_ADATE < '$CheckIn' AND S_DDATE > '$CheckOut') OR (S_ADATE < '$CheckIn' AND ( S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (( S_ADATE BETWEEN '$CheckIn' AND '$CheckOut' ) AND S_DDATE > '$CheckOut')) ";

$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset-1) + $rowsperpage;
$query .= "And rownum between $offset and $limit";

Open in new window

0
 

Author Comment

by:tonyhhisc
ID: 39955992
I have did like this  first  time and no  work  with me  :/ , if  can give all code to  help will add it.

KInd regards,
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39958553
no work  with me
What, exactly, happened?  Did you get any output at all?  Did you get an error message?  If you can give us the SSCCE or at least some clues to go on we might be able to produce better answers.

I believe that MSSQL has a TOP clause.  There is some discussion of this here:
http://forums.asp.net/t/1224860.aspx?SELECT+TOP+MSSQL

There is also OFFSET / FETCH.
http://technet.microsoft.com/en-us/library/gg699618.aspx
0
 

Author Closing Comment

by:tonyhhisc
ID: 39972476
thanks for your help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question