Problems with paging using SQLSRV and PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
happyneil
Forum Newbie
Posts: 8
Joined: Tue Apr 12, 2011 10:56 am

Problems with paging using SQLSRV and PHP

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

?>
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Problems with paging using SQLSRV and PHP

Post 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
happyneil
Forum Newbie
Posts: 8
Joined: Tue Apr 12, 2011 10:56 am

Re: Problems with paging using SQLSRV and PHP

Post 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
fugix
Forum Contributor
Posts: 207
Joined: Fri Mar 18, 2011 8:01 pm

Re: Problems with paging using SQLSRV and PHP

Post by fugix »

Glad you figured it out
Post Reply