Does some one have a good tutorial on how to do pagination with MS SQL , i don’t have problems with MySQL i have done it but the LIMIT parameter in MS SQL doesn’t work.
Any ideas ?
Moderator: General Moderators
Code: Select all
$limit = 7; //we want 7 replies returned per page
$pagelimit= $limit; //pagelimit is used when numbering pages so we can change $limit for the query without affecting page numbering
$limitvalue = ($limit * $page); //tells us where to start so if we are on page 2 we have (7*2)-7 = 7 so we know to start from result 7
if(($totalrows % $limit) != 0) { //if there is a remainder then there is a page with less than $limit replies to be displayed
$pages_less_remainder = $totalrows/$limit; //gives us the number of full pages with $limit replies
settype($pages_less_remainder, 'int'); //convert to an integer to remove bits after the decimal point
$totalpages = $pages_less_remainder;
$totalpages++; //total number of pages is one more than pages_less_remainder
if ($page==$totalpages) { //if we are no the last page set the value of $limit to the number of replies remaining
$limit = $totalrows - ($pages_less_remainder * $limit); //where $totalrows is the total number of rows selected from the table
}
}
$query = "SELECT * FROM (
SELECT TOP $limit * FROM (
SELECT TOP $limitvalue * FROM TableName ORDER BY TableID
) AS newtbl ORDER BY TableID DESC
) AS newtbl2 ORDER BY TableID ASC";
//follow with rest of pagination tutorial found at http://www.phpfreaks.com/tutorials/43/1.php subtituting $pagelimit for $limit