Page 1 of 1

Problems with paging using SQLSRV and PHP

Posted: Wed Apr 20, 2011 11:13 am
by happyneil
Hi there,

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>&nbsp"; 
}

/*DISPLAYING LINKS TO PAGES TOP OF PAGE*/
for($i = 1; $i<=$numOfPages; $i++)  
{  
    $pageLink = "?pageNum=$i";  
    print("<a href=$pageLink>$i</a>&nbsp;&nbsp;");  
}

/* NEXT PAGE NAVIGATION TOP OF PAGE */


// Display Next Page link if applicable. 
if($pageNum < $numOfPages) 
{ 
    $nextPageLink = "?pageNum=".($pageNum + 1); 
    echo "&nbsp;&nbsp;<a href='$nextPageLink'>Next Page</a>"; 
}


/* Close the connection. */
sqlsrv_close( $conn);

?>

Re: Problems with paging using SQLSRV and PHP

Posted: Wed Apr 20, 2011 11:24 am
by fugix
when i do pagination i always set a limit on my query to get the proper amount of items per page..not sure if you could incorporate that into this code

Re: Problems with paging using SQLSRV and PHP

Posted: Thu Apr 21, 2011 3:40 am
by happyneil
Thank you fugix for your quick reply! I managed to work out the problem in the end...Basically I added
while($row_count<10 ) { to to display the table.

Cheers,

Neil

Re: Problems with paging using SQLSRV and PHP

Posted: Thu Apr 21, 2011 5:59 am
by fugix
Glad you figured it out