Jump to content

Php, Mssql 2012, And Pagination


meanerme

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.