next prev query in mssql

Anon

Hello,
I would like to know how to build queries for mssql to get next prev links on pages.
I used to build the query using the limit clause on MySQL but it isn't supported on MsSQL7. How do I find an alternative on MsSQL?
Thank you,
Giorgio Bozio

Anon

I think you mean, how do you split results into like 5 pages, instead of showing them all on one ?

If so, then use / operator to find out how many pages are needed.

IE, if you have 20 results and 6 per page.

20 / 6 = 3 (divides 3 times exactly).

20 % 6 = 2 (ie, remainder 2).

So you need 3 pages of 6 results, and 1 page with 2 results.

Anon

yes, I want to split results into more pages, say 10 results per page, but I need an sql query to SQL server of Microsoft to pick up only the records needed, say I'm in the second page, then I want records from 11 to 20. With MySQL I do the query: "Select * from table where clause = '$clause' Order by orderfield LIMIT 10,10" the "LIMIT" part isn't supported in sql server 7 of MS.
How do I get the same functionality from a query to sql server?

Anon

I don't know of anyway you can.

The way I would do it is to query the server and then use language code to iterate through them.

Let's say this

$result = mysql_query(" SELECT * FROM table WHERE clause = $expr ");

for ($n=0;$n<10;$n++)
{
mysql_fetch_array($result);
}

That would put you at number 10.
Then

for ($n=10;$n<20;$n++)
{
$row = mysql_fetch_array($result);
echo "$row[0] $row[1]......";
}

That would display results 10-19.

Anon

That's what I feared... I think I'll have to take your advise and rewrite the piece of code I made for mysql to adapt to mssql.
thank you,
Giorgio

Anon

And this from the man who said selecting all data is slow.:-)

How about this:
Assuming you have a sane table setup with an auto_increment field for the record ID;

-on the first page you select only the record ID's.
-Print links to the various pages and append a list of recordIDs to each link so you can read those ID's into the next php script.
for page 1 your print the first 20 IDs,
or page 2 you print the second 20.
etc, untill you run out of IDs.

Then when a user clicks on "page 4"
you automatically get the recordIDs of all the records that should be printed on page 4.

That saves you
a) selecting all the data from the database for every page
b) parsing though what could be 100's or 1000's of records to get where you want to go.