[SOLVED] Page Numbering

[mrsquash]

Hello all,

 

I am having a problem with trying to display a set amount of records from my result.

I have modified the Page Numbering tutorial from this site and adapted it to use MSSQL.

I have verified that the correct values for my variables are being passed to the query.

The calculation for the records that should be displayed per page is correct.

The total number of records returned from my query is correct.

And the calculated number of total pages to be displayed is correct.

 

So, initially it displays the first 10 results as it should, and has the pages numbers at the bottom.  The problem is, when I click on a different page number the same 10 results are ALWAYS displayed.  Even though my $page variable IS being updated.

 

Any ideas why my results are not reflecting the page I select?

 

<?php
if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
}
// Define the number of results per page 
$max_results = 10; 
// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results);
echo $from."FROM";
$page_results = $max_results + $from;
echo $page_results."PAGE RESULTS";
  // Query the table and load all of the records into an array.
   $sql = "SELECT DISTINCT * FROM ( 
			SELECT TOP $max_results Value1, Value2 FROM (
				SELECT TOP $page_results Value1, 
				FROM my_table
				WHERE my_table.column = 'P'
			) as newtbl order by credit_card_id desc
		) as newtbl2 order by credit_card_id asc";

			print_r ($sql);
 $result = mssql_query($sql) or die(mssql_error());
 	      //print_r ($result);
 $number_rows = mssql_num_rows($result); 
?>
<table width='780' border='1' align='center' cellpadding='2' cellspacing='2' bordercolor='#000000'>
<?php
if(!empty($result)) {
while ($row = mssql_fetch_array($result)) {
	$id = $row['credit_card_id'];
	$dateTime = $row['date_request_received'];
	//print_r ($id_child);
?>
<tr>
<td width='88' height='13' align='center' class='tblcell'><div align='center'><?php echo "<a href='javascript:editRecord($id)'>$id</a>" ?></div></td>
<td width='224' height='13' align='center' class='tblcell'><div align='center'><?php echo "$dateTime" ?></div></td>
<td width='156' height='13' align='center' class='tblcell'><div align='center'><?php echo "To Be Processed" ?></div></td>
<td width='156' height='13' align='center' class='tblcell'><div align='center'><?php echo "Last Processed By" ?></div></td>
</tr>
<?php
}
}
?>
</table>
<table align="center" width="780" cellpadding="2" cellspacing="2" border="0">
<tr>
<td width='780' height='15' align='center' class='tblcell'><div align='center'><strong>Results: </strong><?php echo "$number_rows"; ?></div></td>
</tr>
</table>
<?php 
// Figure out the total number of results in DB: 
$sql_total= "SELECT * FROM my_table WHERE my_table.column = 'P'";
$tot_result = mssql_query($sql_total) or die(mssql_error());
$total_results = mssql_num_rows($tot_result) or die(mssql_error());
// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results);
echo $max_results."Results";
echo $total_results."Total";
echo $total_pages."pages";
// Build Page Number Hyperlinks 
echo "<center>Select a Page<br />"; 
// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 
// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
} 
echo "</center>";
?>

[chocopi]

Well you just need to put a LIMIT on your query which gets the results

 

<?php
$page = 1;
$max_results = 10;
$start_result = $page*$max_results;
$query = mysql_query("SELECT * FROM tablename WHERE something=something LIMIT $start_results, $max_results DESC") or die(mysql_errror());
?>

 

Now that should put you in the right direction, but you might need ASC instead or DESC.

 

~ Chocopi

[mrsquash]

Chocopi,

 

I WISH I could use LIMIT, but my database is in MSSQL not MYSQL which is why I have to compose the query using the multiple subqueries. MSSQL does not have a function similar to LIMIT.

[mrsquash]

***USING MSSQL Server***

 

The below code will allow you to create previous/next/page links and update your displayed results.

 

This first block of code queries your database

<?php
  if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
}
// Define the number of results per page 
$max_results = 10; 
// Figure out the limit for the query based 
// on the current page number. 
$page_results = intval(($page*$max_results)-$max_results);

// set the order by type
$order_by_criteria = 'my_criteria';
  // Query the table and load all of the records into an array.
  if ($page === 1) {
  	$sql = "SELECT TOP {$max_results} value1, value2
		FROM my_table
		WHERE my_attrib = '??'
		ORDER BY {$order_by_criteria}";
  } else {
    $sql = "SELECT TOP {$max_results} value1, value2
		FROM my_table
		WHERE my_attrib = '??'
		AND PKEY NOT IN (
			SELECT TOP {$page_results} value1
			FROM my_table
			WHERE my_attrib = '??'
			ORDER BY {$order_by_criteria}
		)
		ORDER BY {$order_by_criteria}";
  }

		//	print_r ($sql);
 $result = mssql_query($sql) or die(mssql_error());
 	   //   print_r($result);
 $number_rows = mssql_num_rows($result);
              // This calculates the end record number
 $end_row = intval(($page*$max_results)-$max_results+$number_rows);
              // This calculates the beginning record number
 $start_row = intval(($page*$max_results)-$max_results+1)
?>

 

Insert your desired HTML here for your table layout...loop thruogh your results using while.

 

This second block of code creates your page links

<?php 
// Figure out the total number of results in DB:
$sql_total= "SELECT * FROM my_table WHERE my_attrib = '??'";
$tot_result = mssql_query($sql_total) or die(mssql_error());
$total_results = mssql_num_rows($tot_result) or die(mssql_error());
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
?>
// This table simply echos out the results you are displaying and the total results found
<table align="center" width="780" cellpadding="2" cellspacing="2" border="0">
<tr>
<td width='780' height='15' align='center' class='tblcell'><div align='left'><strong>Displaying Results: </strong><?php echo $start_row."-".$end_row; ?></div></td>
<td width='780' height='15' align='center' class='tblcell'><div align='right'><strong>Total Results: </strong><?php echo $total_results; ?></div></td>
</tr>
</table>
<?php
// Build Page Number Hyperlinks 
echo "<center>Select a Page<br />"; 
// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><< Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 
// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next >></a>"; 
} 
echo "</center>";
?>