Solved

LIMIT FOR MSSQL

Posted on 2014-03-25
5
256 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
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
thanks for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP and Soap 3 26
Not needed 13 53
Creating Image Thumbnails Using PHP 3 14
SQL 2016 Setup - Connectivity Issues 4 11
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to dynamically set the form action using jQuery.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now