我正在尝试使用 php 和 sql server 2008 对我的产品进行分页。我按照这个视频教程来帮助我了解分页的工作原理：
http://www.youtube.com/watch?feature...&v=K8xYGnEOXYc
唯一的问题是本教程是关于MySQL而不是SQL Server 2008
的。这是我到目前为止编写的代码：
$tsql = "SELECT *
FROM products INNER JOIN product_catalogue
ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1'
ORDER BY productID ASC";
$stmt = sqlsrv_query($conn,$tsql);
$nr = sqlsrv_num_rows($stmt); // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
$pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
} else { // If the pn URL variable is not present force it to be value of page number 1
$pn = 1;
}
//This is where we set how many database items to show on each page
$itemsPerPage = 10;
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
$pn = 1; // force it to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
$pn = $lastPage; // force it to be $lastpage's value
}
// This creates the numbers to click in between the next and back buttons
// This section is explained well in the video that accompanies this script
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
} else if ($pn == $lastPage) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> ';
} else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> ';
$centerPages .= ' <span class="pagNumActive">' . $pn . '</span> ';
$centerPages .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> ';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$tsql2 = "SELECT *
FROM products INNER JOIN product_catalogue
ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1'
ORDER BY productID ASC $limit";
$stmt2 = sqlsrv_query($conn,$tsql2);
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
// This shows the user what page they are on, and the total number of pages
$paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. ' ';
// If we are not on page 1 we can place the Back button
if ($pn != 1) {
$previous = $pn - 1;
$paginationDisplay .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
}
// Lay in the clickable numbers display here between the Back and Next links
$paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
// If we are not on the very last page we can place the Next button
if ($pn != $lastPage) {
$nextPage = $pn + 1;
$paginationDisplay .= ' <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
}
}
// Build the Output Section Here
$outputList = '';
while($row = sqlsrv_fetch_array($stmt2)){
$id = $row["productID"];
$product_name = $row["product_name"];
$product_price = $row["product_price"];
$outputList .= '<h1>' . $product_name . '</h1><h2>' . $product_price . ' </h2><hr />';
}
?>
正如您在第二个查询上方看到的那样，我设置了 LIMIT 范围并尝试将$limit变量插入到 SELECT 查询中。不幸的是，这不适用于SQL Server。
所以这是我不知道如何正确转换为SQL Server的代码：
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
$tsql2 = "SELECT *
FROM products INNER JOIN product_catalogue
ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1'
ORDER BY productID ASC $limit";
$stmt2 = sqlsrv_query($conn,$tsql2);
我尝试将其转换为SQL Server应该理解的格式，但由于我对PHP和一般编程非常陌生，因此我无法使其工作
$limit = ($pn - 1) * $itemsPerPage;
$next_page = $itemsPerPage;
$tsql2 = "SELECT TOP $next_page productID, product_name, product_price
FROM products INNER JOIN product_catalogue
ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1' AND productID NOT IN(SELECT TOP $limit productID FROM products INNER JOIN product_catalogue
ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1'
ORDER BY productID ASC)";
$stmt2 = sqlsrv_query($conn,$tsql2);
期待您的回复
谢谢
以下是我们通常用来解决这个问题的解决方案：
/**
* Constructs a offsetted query.
*
* Because of the major differences between MySQL (LIMIT) and
* SQL Server's cursor approach to offsetting queries, this method
* allows an abstraction of this process at application level.
*
* @param int $offset the offset point of the query
* @param int $limit the limit of the query
* @param string $select the fields we're selecting
* @param string $tables the tables we're selecting from
* @param string $order the order by clause of the query
* @param string $where the conditions of the query
*
* @return string
*/
function offset($offset, $limit, $select, $tables, $order, $where='')
{
$ret .= 'SELECT [outer].* FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' . $order .') as ROW_NUMBER,
' . $select . '
FROM ' . $tables . ($where ? ' WHERE ' . $where : '')
.') AS [outer]
WHERE [outer].[ROW_NUMBER] BETWEEN '
. (intval($offset)+1).' AND '.intval($offset+$limit)
. ' ORDER BY [outer].[ROW_NUMBER]';
return $ret;
}
虽然我在PHP方面没有任何好的经验。但我有 ASP.Net MS SQL Server的分页经验。我认为最好创建一个存储过程，该过程将在单个SP中完成所有分页工作，并在PHP中获取结果集。以下是可以帮助您的 SP：
USE [your_db]
GO
CREATE PROCEDURE [dbo].[usp_product_paginate]
@page_number INT, @page_size INT
AS
BEGIN
SELECT ROW_NUMBER() OVER (ORDER BY productID ASC) row_num,productId,productName
INTO #temp
FROM products INNER JOIN product_catalogue ON products.catalogueID = product_catalogue.catalogueID
WHERE category1 = '1';
SELECT productId,productName,
@page_size AS page_size,(SELECT Min(row_num) FROM #temp)AS first_row, (SELECT COUNT(*) FROM #temp) AS tot_row
FROM #temp
WHERE row_num > (@page_number - 1) * @page_size and row_num <= @page_number * @page_size;
END