pagination in php and mysql

Question

I coded pagination code for my site in php and mysql but when there is no record it give error mysql_fetch_array() expects parameter 1 to be resource In this code when the database table is empty than it will Page 0 of 0 Back -1 0
and if the recored is there in database table than it working very fine. my php pagination code is as follows

 $sql = mysql_query("SELECT id, firstname, country FROM myTable ORDER BY id ASC");
$nr = mysql_num_rows($sql);
if (isset($_GET['pn'])) {
$pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); 
} else {
$pn = 1;
} 
$itemsPerPage = 10; 
$lastPage = ceil($nr / $itemsPerPage);
if ($pn < 1) { // If it is less than 1
$pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
$pn = $lastPage; // force it to be $lastpage's value
} 
$centerPages = "";
$sub1 = $pn - 1;
   $sub2 = $pn - 2;
   $add1 = $pn + 1;
     $add2 = $pn + 2;
 if ($pn == 1) {
 $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' .              $add1 . '</a> &nbsp;';
 } else if ($pn == $lastPage) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' .       $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
 } else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' .  $sub2 . '</a> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' .  $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' .  $add1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
 } else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
$centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
$centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
}
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
$sql2 = mysql_query("SELECT id, firstname, country FROM myTable ORDER BY id ASC $limit");

if ($lastPage != "1"){
// This shows the user what page they are on, and the total number of pages
$paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '&nbsp;  &nbsp;  &nbsp; ';
// If we are not on page 1 we can place the Back button
if ($pn != 1) {
    $previous = $pn - 1;
    $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
} 
// Lay in the clickable numbers display here between the Back and Next links
$paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
// If we are not on the very last page we can place the Next button
if ($pn != $lastPage) {
    $nextPage = $pn + 1;
    $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
} 
}
 $outputList = '';
  while($row = mysql_fetch_array($sql2)){ 

$id = $row["id"];
$firstname = $row["firstname"];
$country = $row["country"];

$outputList .= '<h1>' . $firstname . '</h1><h2>' . $country . ' </h2><hr />';

} 

 <h2>Total Items: <?php echo $nr; ?></h2>

  <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF;    border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
  <div style="margin-left:64px; margin-right:64px;"><?php print "$outputList"; ?></div>
  <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>

Can some one suggest Good pagination with page 1 of 1 from 1 option ?

You are doing it wrong. Put it on paper and then retry. The entire pagination code should be at max 15 lines. — Ionut Flavius Pogacian
– Ionut Flavius Pogacian, Commented Aug 13, 2012 at 11:55
I put $pn = 0; if dont GET pn so the error is solved but the option of next page and 2nd page is comes. — Mehuldabhi
– Mehuldabhi, Commented Aug 13, 2012 at 11:56

Bhuvan Rikka · Accepted Answer · 2012-08-13 11:56:35Z

Try this code.

'Next' and 'Previous' links:

echo "<h5>Showing ".$start." to ".($start+$limit)." Records of ".$count." Records</h5>";
if($start<=($count-$limit))
{
    echo '<a style="float:right" href="'.$_SERVER['PHP_SELF'].'?start='.($start+$limit).'&limit='.$limit.'&column='.$column.'&order='.$order.'&status='.$status.'&b='.$b.'&type='.$type.'&search='.$search.'"><t1>Next</t1></a>';
}


$prev = $start-$limit;
if ($prev >= 0)
{
    echo '<a style="float:left" href="'.$_SERVER['PHP_SELF'].'?start='.$prev.'&limit='.$limit.'&column='.$column.'&order='.$order.'&status='.$status.'&b='.$b.'&type='.$type.'&search='.$search.'"><t2>Previous</t2></a>';
}

Page numbers:

$i=0;
$l=1;
echo "<p align='center'>";
for($i=0;$i < $count;$i=$i+$limit)
{
    if($i <> $start)
    {
        echo "<a href='listing_test.php?start=$i&limit=$limit'><font face='Verdana' size='2'><b>&nbsp;$l&nbsp;</b></font></a> ";
    }
    else
    {
        echo "<font face='Verdana' size='4' color=#2E9AFE ><b>&nbsp;$l&nbsp;</b></font>";
    }        
$l=$l+1;
}
echo "</p>";

This worked perfectly for me!

Here $count is total number of rows in table, $limit is the LIMIT to be displayed on each page

VinayKumarD · Accepted Answer · 2012-08-13 11:57:55Z

0

You must check if the resultset is empty
this code may help you.. Surround your pagination code with
if ($nr==1){ //code if no records are available}else{//if data is available}

answered Aug 13, 2012 at 11:57

VinayKumarD

214 bronze badges

Collectives™ on Stack Overflow

pagination in php and mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related