CODEWITHSUNDEEP
phpsql-serversqlsrv
per källström
per källström

Reputation: 169

Pagination with Microsoft sql & PHP

Trying to do some pageintaion with mssql & php but i cant seem to get it working... i get no errors, so i cant really find out what im doing wrong, am i missing anything obvious?

 <?php 
error_reporting(E_ALL); ini_set('display_errors', 1);
 $dbhost = 'localhost';
 $dbuser = '*****';
$dbpass = '******';
$db = 'blog';
$connectionInfo = array( "UID"=>$dbuser,                            
"PWD"=>$dbpass,                            
"Database"=>$db, "CharacterSet" => "UTF-8"); 
$connect_db = sqlsrv_connect ( $dbhost, $connectionInfo ) or die(sqlsrv_errors());

if ((!isset($_GET['pagenum'])) || (!is_numeric($_GET['pagenum'])) || ($_GET['pagenum'] < 1)) { $pagenum = 1; }
else { $pagenum = $_GET['pagenum']; }

$result = sqlsrv_query ($connect_db,"SELECT blogID FROM blog_posts") or die(sqlsrv_errors());

$rows = sqlsrv_num_rows($result);

$page_rows = 2; 

$last = ceil($rows/$page_rows); 
if (($pagenum > $last) && ($last > 0)) { $pagenum = $last; }

$max = ($pagenum - 1) * $page_rows;

$result2 = sqlsrv_query(($connect_db,"SELECT TOP $page_rows FROM blog_posts WHERE blogID NOT IN (SELECT TOP $max blogID FROM blog_posts ORDER BY blogID ASC) ORDER BY blogID ASC") or die(sqlsrv_errors()); 

while($info = sqlsrv_fetch_array( $result2, SQLSRV_FETCH_BOTH )) 
{ 
    print $info['blogID']; 
    echo "<br>";
} 
echo "<p>";
echo " --Page $pagenum of $last-- <p>";

if ($pagenum == 1) { } 
    else 
    {
        echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
        echo " ";
        $previous = $pagenum-1;
        echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
    } 
    echo " ---- ";
    if ($pagenum == $last) 
    {
    } 
    else {
        $next = $pagenum+1;
        echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
        echo " ";
        echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
    } 
    ?>

blogID is the unique id of every post in the db

Upvotes: 1

Views: 1590

Answers (1)

BugFinder
BugFinder

Reputation: 17858

Non technical answer (eg, not writing all your code for you)

You've currently got the code that says $max = (page number-1) * length of page

-if page = 1, then max = 0, so there is no data for page 1

-for page 2, you'd have 1 page length of stuff

-for page 3, you'd have 2 page lengths of stuff

clearly - this isnt what you meant. What you meant to do was select the number of rows from end of previous page, to the end of the current page. MSSQL is less good at this

firstly

you need a min and max. to show from

$min = (page number -1 * pagelength)+1 (eg not the same record at end of one page and the beginning of the next) $max = page number * pagelength

if page length was 5, a page view might be min record 21, and max 25

you can then use SQL like SELECT * FROM ( <your query here> ) AS RowConstrainedResult WHERE RowNum >= $min AND RowNum < $max ORDER BY RowNum

your current select statement only gets a blog id - so its going to be a rather boring page but.. thats your call - but hopefully you get the idea.

You would also need to know the number of records, as you'd need to decide if there are more pages after all.

Upvotes: 1

Related Questions