1
\$\begingroup\$

I am having an issue where I am doing a query and it is coming back with about 100 pages, I was hoping to have it come back with 1,2,3...30,31...99,100 instead of 1,2,3,4,5,6,7,8,etc. please see below for working code that returns back results as unwanted format, I am new here so if this question is written wrong please let me know and I will make necessary changes , thanks in advance:

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

// Set the number of rows to be returned on a page.
$rowsPerPage = 30;

// Connect to the server.
$serverName = 'test';
$connOptions = array("Database"=>"test");
$conn = sqlsrv_connect($serverName, $connOptions);
if (!$conn)
die( print_r( sqlsrv_errors(), true));

//  Define and execute the query.  
// Note that the query is executed with a "scrollable" cursor.
$sql = "select * from info";

$stmt = sqlsrv_query($conn, $sql, array(), array( "Scrollable" => 'static' ));
if ( !$stmt )
die( print_r( sqlsrv_errors(), true));

// Display the selected page of data.
echo "<table border='1px' align='center'>";
$pageNum = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$page = getPage($stmt, $pageNum, $rowsPerPage);

foreach($page as $row)
echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td></tr>";

echo "</table>";
?>
<table align='center'>
<?php
// Get the total number of rows returned by the query.
// Display links to "pages" of rows.
$rowsReturned = sqlsrv_num_rows($stmt);
if($rowsReturned === false)
die( print_r( sqlsrv_errors(), true));
elseif($rowsReturned == 0)
{
echo "No rows returned.";
exit();
}
else
{     
// Display page links.
$numOfPages = ceil($rowsReturned/$rowsPerPage);
for($i = 1; $i<=$numOfPages; $i++)
{
 $pageLink = "?pageNum=$i";
 print("<a href=$pageLink>$i</a>&nbsp;&nbsp;");
}
echo "<br/><br/>";
}   



 sqlsrv_close( $conn );
\$\endgroup\$
1
\$\begingroup\$

Do you just mean the links at the bottom of your page? If so you have this code:

// Display page links.
$numOfPages = ceil($rowsReturned/$rowsPerPage);
for($i = 1; $i<=$numOfPages; $i++)
{
    $pageLink = "?pageNum=$i";
    print("<a href=$pageLink>$i</a>&nbsp;&nbsp;");
}

Modify this to render just the first three links, then your ellipses (...) separator and then the final page number.

EDIT to add pseudo code
This is messy but hopefully gives you the idea, create your function, make it work and then post a new question asking how to make it prettier (for example tidy up the ifs, add logic to only render a single set of ellipses, don't repeat any rendering code).

$numOfPages = ceil($rowsReturned/$rowsPerPage);
$renderedEllipses = false;
for($i = 1; $i<=$numOfPages; $i++)
{
    //If you always want to render pages 1 - 3
    if($i < 4) {
        //render link
    }

    //If you always want to render current page number
    else if($i == $pageNum) {
        //render link
        //reset ellipses
        $renderedEllipses = false;
    }

    //if you always want the last page number
    else if ($i == $numOfPages - 1) {
        //render link
    }

    //make sure you only do this once per ellipses group
    else {
      if (!$renderedEllipses){
          print("...");
          $renderedEllipses = true;
      }
    }
}

EDIT added ellipses test
The $renderedEllipses flag will stop hundreds of ellipses being rendered.

\$\endgroup\$
  • \$\begingroup\$ Hi JohnMark12, I appreciate your help with this unfortunately I am stuck on how to do the ellipses as if user goes to page 8 it will need to change to 1,2,3...8,9...99,100 and if user goes to page 30 then 1,2,3..30,31...,99,100 if that makes sense, any help would be highly appreciated. @JohnMark13 \$\endgroup\$ – phpyesphp Sep 4 '13 at 13:53
  • \$\begingroup\$ John Awesome , this is very close to what I need however I need to render pages inbetween current page and last page, any idea how to achieve this bud, you are a big help! - @JohnMark13 \$\endgroup\$ – phpyesphp Sep 4 '13 at 16:15
  • \$\begingroup\$ I am going to mark your answer as correct as it helped me to achieve my end goal, Thanks for your help bud! \$\endgroup\$ – phpyesphp Sep 4 '13 at 16:22
  • \$\begingroup\$ Hi. Consider the $i == $pageNum condition which renders a link to the current page. Then consider something like if(abs($i - $pageNum) == 1) which will return true for numbers one either side of the current page. Onwards! \$\endgroup\$ – JohnMark13 Sep 4 '13 at 16:24
  • \$\begingroup\$ 1 last question my ellipses is producing alot of dots so for some reason it is got a string like this : 1,2,3...................................65,.................................129,130 any ideas how to achieve this I am satisfied otherwise :)! - @JohnMark13 \$\endgroup\$ – phpyesphp Sep 4 '13 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.