[RESOLVED] Pagination + SQL Server

[obrienkev]

Hi all,
How do I convert the below to work for MS SQL Server?
My problem is using $limit - creating a pagination system.

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

// Perform Query
$query = "SELECT question_ID, question_text ".
			 	 "FROM questions ".
				 "ORDER BY id ".
				 "DESC ".
				 "$limit";

[obrienkev]

This works now..

/******************************
 Pagination Setup(b)
******************************/
$query_pag = "SELECT count(*) ".
					 	 "FROM questions";
$result_pag = mssql_query($query_pag) or die('Select Count Error');
$query_data = mssql_fetch_row($result_pag);
$numrows = $query_data[0];

$rows_per_page = 1;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if

$limit = ($pageno - 1) * $rows_per_page;
$next_page = $rows_per_page;
/*****************************/

// 1) Perform Question Query
$query = "SELECT TOP $next_page question_ID, question_text ".
			 	 "FROM questions ".
				 "WHERE question_ID NOT IN(SELECT TOP $limit question_ID ".
				 												"FROM questions ".
																"ORDER BY question_ID ".
																"ASC)";
//echo $query;
$result = mssql_query($query) or die('Select Query Error');

[ionescu_raul]

obrienkev wrote:

Hi all,
How do I convert the below to work for MS SQL Server?
My problem is using $limit - creating a pagination system.

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

// Perform Query
$query = "SELECT question_ID, question_text ".
			 	 "FROM questions ".
				 "ORDER BY id ".
				 "DESC ".
				 "$limit";

If you want to take a look at an OOP solution for PHP, check this link. 😉