Php, Mssql 2012, And Pagination

[meanerme]

Hi guys!

 

This is my first post here, I used to post a bit a long time ago on PHP Builders but the site appears to have mostly died.

 

Recently I started my first project working with MSSQL (I always worked with MySQL before) and while I have found some differences and hurdles I had to jump over and learn about some were frustrating like paging query results with having LIMIT available in MSSQL.

 

I was wondering with the new OFFSET and FETCH NEXT introduced with MSSQL 2012 if anyone had used these to setup their pagination on the pages and if so, do you have a basic example of how it was done? I learned about this new feature in MSSQL 2012 today so I upgraded the database in hopes it would be simpler to implement the paging of the results.

 

Thanks!

[kicken]

Near as I can tell from the documentation the way you would use the offset feature is like so:

 

SELECT
 col1, col2, col3
FROM table
ORDER BY
  col1 OFFSET 0 ROWS FETCH 10 ROWS ONLY

 

Would be equivalent to the mysql query

SELECT
 col1, col2, col3
FROM table
ORDER BY
  col1
LIMIT 0,10

 

I only have sql server 2008, so I can't actually try anything.  For 2008 one would use:

SELECT * FROM (
  SELECT
   ROW_NUMBER() OVER (ORDER BY col1) as rowNumber, col1, col2, col3
  FROM table
  ORDER BY
    col1
) page
WHERE
  rowNumber BETWEEN 1 AND 10

[meanerme]

Thanks Kicken, the document looks to be pretty thorough. I didn't get a chance to play with the database yesterday but now that the upgrade has finished I should be able to play around with it and make more sense. Seeing how close this is to MySQL now, I should be able to make a couple of change to my pagination class to get it to work!

 

Cheers!