CODEWITHSUNDEEP
phppagination
Michael Kweku Ayer
Michael Kweku Ayer

Reputation: 27

php pagination: one record at a time on each page

the code below is to display content from a table, one at a time, and the navigation buttons used to navigate as required. But currently it is displaying all the contents at the same time. Kindly help me fix it. Thanks in advance.

<?php //paging starts here;

// how many rows to show per page
$rowsPerPage = 1;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if ( isset($_GET['page']) ) {
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;


$query = "SELECT ap_id, ap_text FROM approach";
$result = mysqli_query($connect, $query) or die('Error : ' . myqli_connect_error());
?>

<table width="99%" border="0" align="center" cellpadding="3"  cellspacing="1" bgcolor="#999999">

<?php
while ( list($id, $text) = mysqli_fetch_array($result, MYSQLI_NUM) ) {
    ?>
    <tr bgcolor="#FFFFFF"> 
        <td width="619" class="mainpageHeadlines"> 
        <?php echo $text;?>  </td>
        <td width="142" align="center" class="mainpageHeadlines">&nbsp;</td>
    </tr>
    <?php
}
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(ap_title) AS numrows FROM approach";
$result  = mysqli_query($connect, $query) or die('Error, query failed here:   ' . mysqli_connect_error());
$row     = mysqli_fetch_array($result, MYSQLI_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ( $pageNum > 1 ) {
    $page = $pageNum - 1;
    $prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

    $first = " <a href=\"$self?page=1\">[First Page]</a> ";
} else {
    $prev  = ' [Prev] ';       // we're on page one, don't enable 'previous'    link
    $first = ' [First Page] '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ( $pageNum < $maxPage ) {
    $page = $pageNum + 1;
    $next = " <a href=\"$self?page=$page\">[Next]</a> ";

    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} else {
    $next = ' [Next] ';      // we're on the last page, don't enable 'next' link
    $last = ' [Last Page] '; // nor 'last page' link
}

// print the page navigation link
//echo $first . $prev . " Showing page <strong>$pageNum</strong> of       <strong>$maxPage</strong> pages " . $next . $last;


include 'db/closedb.php';
?>
</table>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
        <td align="center" class="smaller">   
            <?php 

            // print the page navigation link
            echo '<br>';
            echo $first . $prev . " Page <strong>$pageNum</strong> of    <strong>$maxPage</strong>" . $next . $last;  

            ?>

Upvotes: 1

Views: 1064

Answers (2)

ak_
ak_

Reputation: 2815

Try SQL OFFSET with your post variable.

$query = "SELECT ap_id, ap_text FROM approach OFFSET " . $offset;

Also, make sure you sanitize your queries to prevent SQL injection:

$pageNum = $_GET['page'];
should be:
$pageNum = mysql_escape_string($_GET['page']);

Upvotes: 0

MatejG
MatejG

Reputation: 1423

You need to LIMIT your query by page you are on. For example:

$query = "SELECT ap_id, ap_text FROM approach LIMIT $offset, $rowsPerPage";

Upvotes: 1

Related Questions