Page 1 of 1

MSSQL-PHP-Pagination

Posted: Tue Jan 11, 2005 11:35 pm
by omelin
Hi,

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 ?
:?

Posted: Tue Jan 11, 2005 11:43 pm
by feyd

Posted: Wed Jan 12, 2005 7:01 am
by primate
This works well for me, I added a bit to deal with the remainder so you only display the remaining results on the last page rather than $limit results which would contain results you'd already seen

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
Hope this helps ;)

Thanks for the help

Posted: Wed Jan 12, 2005 2:38 pm
by omelin
The information help a lot , thank you for the information.