Pagination with SQL Server 2008

manhunt234 · May 6, 2012

Hello

I am trying to paginate my products by using php and sql server 2008. I followed this video tutorial to help me understand how pagination works:

The only problem is that this tutorial is about MySQL and not SQL Server 2008

Here is my code that I have written so far:

$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 />'; 
} 
?>

As you can see right above my second query I set the LIMIT range and try to insert the $limit variable into the SELECT query. Unfortunately that doesn't work with SQL Server.

So this is the code that I don't know how to convert to SQL Server properly:

$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);

I tried converting it into the format SQL Server should understand but as I'm very new to PHP and to programming in general, I was not able to get it to work

$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);

Looking forward to your reply

Thank you

kicken · May 7, 2012

For SQL server you need to do sub-query setup and select the TOP X records sorted in the proper order, then from that result set select the TOP Y records sorted in reverse order, then select them all again sorted in the proper order again. It's a bit of a pain in the ass.

For example:

SELECT * FROM (
SELECT TOP $itemsPerPage * 
FROM (
	SELECT TOP $maxRecords 
		productID, product_name, product_price 
	FROM products 
	INNER JOIN product_catalogue ON products.catalogueID = product_catalogue.catalogueID 
	WHERE 
		category1 = '1' 
	ORDER BY productID ASC
) tbl 
ORDER BY productID DESC
) tbl
ORDER BY productID ASC

Where $maxRecords = $pn*$itemsPerPage

manhunt234 · May 7, 2012

Thank you very much for your reply, kicken

Here is my updated code:

$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>  ';
}

$maxRecords = $pn*$itemsPerPage;

$tsql2 = "SELECT * FROM (
SELECT TOP $itemsPerPage * 
FROM (
	SELECT TOP $maxRecords 
		productID, product_name, product_price 
	FROM products 
	INNER JOIN product_catalogue ON products.catalogueID = product_catalogue.catalogueID 
	WHERE 
		category1 = '1' 
	ORDER BY productID ASC
) tbl 
ORDER BY productID DESC
) tbl
ORDER BY productID ASC";

$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 />';
}
?>

And this is where I output everything

<body>
 <div style="margin-left:64px; margin-right:64px;">
     <h2>Total Items: <?php echo $nr; ?></h2>
   </div> 
      <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
      <div style="margin-left:64px; margin-right:64px;"><?php print $outputList; ?></div>
      <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>

</body>

Unfortunately I still get no results displayed on my page. I've been trying to get this pagination to work for the past week and no luck so far

Do you have any ideas why I get no records displayed on my page?

Thank you

kicken · May 7, 2012

Echo out your query and run it in management studio to make sure you are getting the correct results back. If it is giving you the right results then change your PHP so it just dumps them out to make sure you are getting them there properly:

while($row = sqlsrv_fetch_array($stmt2)){ 
     var_dump($row);
}

If that comes back ok, show us what kind of output you are getting.

manhunt234 · May 8, 2012

Thank you for your reply, kicken

I'm sorry but I'm not exactly sure how to echo out my query and run it in management studio?

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 />';
}

<?php echo $outputList; ?>

Is that what you mean by echoing out a query?

I tried running this query in management studio

SELECT * FROM (
SELECT TOP $itemsPerPage * 
FROM (
	SELECT TOP $maxRecords 
		productID, product_name, product_price 
	FROM products 
	INNER JOIN product_catalogue ON products.catalogueID = product_catalogue.catalogueID 
	WHERE 
		category1 = '1' 
	ORDER BY productID ASC
) tbl 
ORDER BY productID DESC
) tbl
ORDER BY productID ASC

But I obviously got an error message saying "Incorrect syntax near '$itemsPerPage'"

I tried "var_dump($row);" that you suggested but I still get no results at all

kicken · May 8, 2012

Is that what you mean by echoing out a query?

No,

$tsql2 = "SELECT * FROM (
SELECT TOP $itemsPerPage * 
FROM (
	SELECT TOP $maxRecords 
		productID, product_name, product_price 
	FROM products 
	INNER JOIN product_catalogue ON products.catalogueID = product_catalogue.catalogueID 
	WHERE 
		category1 = '1' 
	ORDER BY productID ASC
) tbl 
ORDER BY productID DESC
) tbl
ORDER BY productID ASC";

var_dump($tsql2); //Outputs the query

$stmt2 = sqlsrv_query($conn,$tsql2);

That is what I meant. var_dump will display the query that is about to be run to your page. Copy and paste that into management studio and run it.

manhunt234 · May 13, 2012

Sorry for a very late reply, I've been really busy with my project so I didn't have a chance to reply to your message

I have changed my code entirely and managed to get pagination to work, but I have a small problem with it displaying images in a table.

I am trying to display the images that I get from the database in a dynamically generated table that will display 3 columns of images in a row like so

"Image1 Image 2 Image 3

Image 4 Image 5 Image 6

etc"

<?php
require_once ('includes/connect.inc.php');

$items_on_page = 12; 

if(isset($_GET['page']))
{
$page = $_GET['page'];
$start_from = ($_GET['page']-1)*$items_on_page;
}else
{
$page = 1;
$start_from = 0;
}

$sql = "SELECT p.productID, p.catalogueID, p.colour1, p.colour2, p.colour3, ROW_NUMBER() OVER(ORDER BY p.productID ASC) AS rownumber 
FROM products p JOIN product_catalogue c ON p.catalogueID = c.catalogueID 
WHERE p.category1=2 OR p.category2=2"; 

$params = array($_GET['category1']&&($_GET['category2']));

$stmt = sqlsrv_query( $conn, $sql, $params);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql2 = "SELECT COUNT(productID) AS 'rowcount' FROM products WHERE category1=2 OR category2=2";
$stmt2 = sqlsrv_query( $conn, $sql2,$params);
if( $stmt2 === false ) {
     die( print_r( sqlsrv_errors(), true));
}


while( $row2 = sqlsrv_fetch_array( $stmt2)){$total_records = $row2['rowcount'];}

$total_pages = ceil($total_records / $items_on_page);

?>

<?php
					$dynamicList = "";
					while( $row = sqlsrv_fetch_array($stmt)) 
						{	
							$rownumber = $row['rownumber'];
							$end_at = $items_on_page + $start_from;

							if (($rownumber>$start_from)&&($rownumber<=$end_at))
							{
								$prod_id = $row['productID'];
								$cat_id = $row['catalogueID'];
								$col_id1 = $row['colour1'];
								$col_id2 = $row['colour2'];
								$col_id3 = $row['colour3'];

								$sql3 = "SELECT * FROM product_colours WHERE colourID =? OR colourID =? OR colourID =?"; 
								$params3 = array($col_id1,$col_id2,$col_id3);
								$stmt3 = sqlsrv_query( $conn, $sql3, $params3);

								$count = 1;
								while( $row3 = sqlsrv_fetch_array( $stmt3)) 
								{
									switch ($count)
									{
										case 1:$colour1 = $row3['colour_description'];break;
										case 2:$colour2 = $row3['colour_description'];break;
										case 3:$colour3 = $row3['colour_description'];break;
									}
									$count++;
								}

								$sql3 = "SELECT catalogueID, product_name, product_price, description FROM product_catalogue WHERE catalogueID = ?"; 
								$params3 = array($cat_id);
								$stmt3 = sqlsrv_query( $conn, $sql3, $params3);

								$columncount = 0;
$dynamicList = '<table width="744" border="0" cellpadding="6"><tr>';
while( $row3 = sqlsrv_fetch_array( $stmt3)) 
{
  $prod_name = $row3['product_name'];
  $prod_price = $row3['product_price'];
  $prod_desc = $row3['description'];

  $dynamicList .= '<td width="135"><a href="product_details_women.php?productID=' . $prod_id . '">
      <img src="images/products/Small/Women/' . $prod_id . '.jpg" alt="' . $prod_name . '" width="129" height="169" border="0">
    </a>
  </td>

  <td width="593" valign="top">' . $prod_name . '<br>
  £' . $prod_price . '<br>
  <a href="product_details_women.php?productID=' . $prod_id . '">View Product Details</a></td>';

if($columncount == 2)
{
$dynamicList .= '</tr><tr>';
$columncount = 0;
}
else
{
  $columncount++; 
}
}


$dynamicList .= '</tr></table>';

echo $dynamicList;



}

}

?>

 <?php
                        

					function Pagination($x)
					{   
						$pageURL = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
						//$pageURL = $_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];							
						$pageURL = preg_replace("/\b&page=([0-9]{1,4})/","",$pageURL);							
						return $pageURL."&page=".$x;                
					}

					if ($page != 1)
					{
						echo "<a href='".Pagination($page-1)."'>Previous</a>";
					} 
					else
					{
						echo "Previous";
					}

					echo " | ";
					for ($i=1; $i<=$total_pages; $i++) 
					{ 
						if ($i != $page)
						{
							echo "<a href='".Pagination($i)."'> ".$i." </a>"; 
						}
						else
						{
							echo " ".$i." ";
						}
					} 
					echo " | ";

					if ($page != $total_pages)
					{
						echo "<a href='".Pagination($page+1)."'>Next</a>";
					} 
					else
					{
						echo "Next";
					}
					?>

I know it has something to do with me using tables to display images in rows but I just can't find the problem anywhere

Sign In

Pagination with SQL Server 2008

Recommended Posts

manhunt234

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

manhunt234

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

manhunt234

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

manhunt234

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information