Is there anyone that can help me with this code. Basically I want to create a table with pagination. At the moment, the code that I am using (adapted from
http://blogs.msdn.com/b/brian_swan/arch ... ified.aspx) shows me the whole table (all 100 value... Not 10 per page as stored in the $rowsPerPage).
The correct number of pages are appearing with the next (and previous button when not on 1st page). When I click on page 2... 90 values are showing. When page 3 is clicked... 80 values are showing.......... and page 9 shows 9 values.
Can anyone advice me on what might be wrong with my code? I've been staring at it for days and can't figure it out. If anything is unclear then please let me know...
Cheers,
Neil
Code: Select all
<?php
/*data base connection */
include "mssqlconnection.inc.php";
/* SQL query */
$tsql = (" SELECT TOP 100 tie_parent_id, CAST(geo_post AS varchar(6)) + '.' + CAST(geo_sample AS varchar(6)) AS Mile, gps_lat, gps_long, rotten, split, wheel_cut, broken, quality
FROM V6_HOLLTS479_20101015_subset.dbo.rvresults_tie_parent
");
$stmt = sqlsrv_query($conn,$tsql, array(), array( "Scrollable" => 'static'));
if( $stmt === false)
{
echo "Error in query preparation/execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/* DETERMINING THE NUMBER OF ROWS (AND PAGES) */
// Set the number of rows to be returned on a page.
$rowsPerPage = 10;
// Get the total number of rows returned by the query.
$rowsReturned = sqlsrv_num_rows($stmt);
if($rowsReturned === false)
die( print_r( sqlsrv_errors(), true));
elseif($rowsReturned == 0)
{
echo "No rows returned.";
exit();
}
else
{
/* Calculate number of pages. */
$numOfPages = ceil($rowsReturned/$rowsPerPage);
}
/* FUNCTION FOR PAGING */
function getPage($stmt, $pageNum, $rowsPerPage)
{
$offset = ($pageNum - 1) * $rowsPerPage;
$rows = array();
$i = 0;
while($row = sqlsrv_fetch_array($stmt,
SQLSRV_FETCH_NUMERIC,
SQLSRV_SCROLL_ABSOLUTE,
$offset + $i)
&& $i < $rowsPerPage)
{
array_push($rows, $row);
$i++;
}
return $rows;
}
// Display the selected page of data.
echo "<table width='800' border='0'>";
echo "<tr> <th>Tie ID</th> <th>Mile/Yard</th> <th>GPS Lat</th><th>GPS Long</th><th>Rotten</th><th>Split</th><th>WheelCut</th> <th>Broken</th><th>Quality</th> </tr>";
// keeps getting the next row until there are no more to get
$pageNum = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$page = getPage($stmt, $pageNum, $rowsPerPage);
$color1 = "#ffffff";
$color2 = "#edf5fa";
$row_count = "0";
while($row = sqlsrv_fetch_array( $stmt )) {
$tie_parent_id = $row["tie_parent_id"];
$geo_post = $row["Mile"];
$lat =$row["gps_lat"];
$long =$row["gps_long"];
$rotten =$row["rotten"];
$split =$row["split"];
$wheelcut =$row["wheel_cut"];
$broken =$row["broken"];
$quality =$row["quality"];
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["tie_parent_id"]; ?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["Mile"];?> </td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["gps_lat"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["gps_long"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["rotten"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["split"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["wheel_cut"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["broken"];?></td>
<td bgcolor="<?php echo $row_color ?>">
<?php echo $row["quality"];?></td>
</td></tr>
<?php
$row_count++;
}
/* PREVIOUS PAGE NAVIGATION TOP OF PAGE */
// Display Previous Page link if applicable.
if($pageNum > 1)
{
$prevPageLink = "?pageNum=".($pageNum - 1);
echo "<a href='$prevPageLink'>Previous Page</a> ";
}
/*DISPLAYING LINKS TO PAGES TOP OF PAGE*/
for($i = 1; $i<=$numOfPages; $i++)
{
$pageLink = "?pageNum=$i";
print("<a href=$pageLink>$i</a> ");
}
/* NEXT PAGE NAVIGATION TOP OF PAGE */
// Display Next Page link if applicable.
if($pageNum < $numOfPages)
{
$nextPageLink = "?pageNum=".($pageNum + 1);
echo " <a href='$nextPageLink'>Next Page</a>";
}
/* Close the connection. */
sqlsrv_close( $conn);
?>