Solved

LIMIT FOR MSSQL

Posted on 2014-03-25
5
261 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 110

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to count occurrences of each item in an array.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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