Limit pagination page number

Question

$objConnect = mysql_connect("localhost","root","") or die(mysql_error());
$objDB = mysql_select_db("Test");

$strSQL = "SELECT * FROM UserAddedRecord WHERE (Name LIKE '%".$getname."%' and State LIKE '%".$getstate."%' and Cell LIKE '%".$getcell."%' and Custgroup LIKE '%".$getgroup."%') AND user_id=$id";

$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
$Num_Rows = mysql_num_rows($objQuery);

$Per_Page = 5;   

if (!isset($_GET['Page'])) {
    $Page = 1;
} else {
    $Page = $_GET['Page'];
}

$Prev_Page = $Page - 1;
$Next_Page = $Page + 1;

$Page_Start = (($Per_Page * $Page) - $Per_Page);
if ($Num_Rows <= $Per_Page) {
    $Num_Pages = 1;
} elseif (($Num_Rows % $Per_Page) == 0) {
    $Num_Pages = ($Num_Rows / $Per_Page) ;
} else {
    $Num_Pages = ($Num_Rows / $Per_Page) + 1;
    $Num_Pages = (int) $Num_Pages;
}


$strSQL .=" order  by addedrec_ID DESC LIMIT $Page_Start , $Per_Page";
$objQuery  = mysql_query($strSQL) or trigger_error(mysql_error());;

if ($Prev_Page) {
    echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page&txtName=$getname&txtState=$getstate&txtCell=$getcell&txtGroup=$getgroup'><< Back</a> ";
}

for ($i=1; $i <= $Num_Pages; $i++) {
    if ($i != $Page) {
        echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$i&txtName=$getname&txtState=$getstate&txtCell=$getcell&txtGroup=$getgroup'>$i</a> ";
    } else {
        echo "<b> $i </b>"; 
    }
}

if ($Page!=$Num_Pages) {
    echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page&txtName=$getname&txtState=$getstate&txtCell=$getcell&txtGroup=$getgroup'>Next>></a> ";        
}

mysql_close($objConnect);

This is the code I use to create pagination page for data search.But I just noticed that I have too many records then it will have too many page numbers.I'm trying to limit the page number shown,and look for many examples ..it have many ways to do it but I still don't have idea how to limit it for my method...

Khabarovsk

Answer 1

Ok if you mean show something like

Prev 1 2 3 4 5 6 .. 40 41 Next 
Prev 1 2 .. 6 7 8 9 10 .. 40 41 Next

First thing we need to is create a function that can process what we need to output the pagination. Heres a function I use and it works well.

function get_paging_info($tot_rows,$pp,$curr_page)
{
    $pages = ceil($tot_rows / $pp); // calc pages

    $data = array(); // start out array
    $data['si']        = ($curr_page * $pp) - $pp; // what row to start at
    $data['pages']     = $pages;                   // add the pages
    $data['curr_page'] = $curr_page;               // Whats the current page

    return $data; //return the paging data

}

Now this function is pretty solid and works very well for me.

So you pass this function

• $tot_rows = counted rows for query
• $pp = items per page
• $curr_page = the current page number

Ok, now that you have the data you need, you'll need to display it.

Heres what I use and please read it before you think, 'ah, it's too long'. It's actually very simple.

Heres a snapshot of what it will return

    <!-- Create the query -->
    <?php $count = mysql_fetch_assoc( mysql_query ( "SELECT COUNT( rows ) as count FROM table" ) ) ;

    <?php $count = $count[0]['count']; ?>

<!-- Call our function from above -->
<?php $paging_info = get_paging_info($count,5,34); ?>


<p>
    <!-- If the current page is more than 1, show the First and Previous links -->
    <?php if($paging_info['curr_page'] > 1) : ?>
        <a href='' title='Page 1'>First</a>
        <a href='' title='Page <?php echo ($paging_info['curr_page'] - 1); ?>'>Prev</a>
    <?php endif; ?>



    <?php
        //setup starting point

        //$max is equal to number of links shown
        $max = 7;
        if($paging_info['curr_page'] < $max)
            $sp = 1;
        elseif($paging_info['curr_page'] >= ($paging_info['pages'] - floor($max / 2)) )
            $sp = $paging_info['pages'] - $max + 1;
        elseif($paging_info['curr_page'] >= $max)
            $sp = $paging_info['curr_page']  - floor($max/2);
    ?>

    <!-- If the current page >= $max then show link to 1st page -->
    <?php if($paging_info['curr_page'] >= $max) : ?>

        <a href='' title='Page 1'>1</a>
        ..

    <?php endif; ?>

    <!-- Loop though max number of pages shown and show links either side equal to $max / 2 -->
    <?php for($i = $sp; $i <= ($sp + $max -1);$i++) : ?>

        <?php
            if($i > $paging_info['pages'])
                continue;
        ?>

        <?php if($paging_info['curr_page'] == $i) : ?>

            <span class='bold'><?php echo $i; ?></span>

        <?php else : ?>

            <a href='' title='Page <?php echo $i; ?>'><?php echo $i; ?></a>

        <?php endif; ?>

    <?php endfor; ?>


    <!-- If the current page is less than say the last page minus $max pages divided by 2-->
    <?php if($paging_info['curr_page'] < ($paging_info['pages'] - floor($max / 2))) : ?>

        ..
        <a href='' title='Page <?php echo $paging_info['pages']; ?>'><?php echo $paging_info['pages']; ?></a>

    <?php endif; ?>

    <!-- Show last two pages if we're not near them -->
    <?php if($paging_info['curr_page'] < $paging_info['pages']) : ?>

        <a href='<?php echo str_replace('/page'.$paging_info['curr_page'], '', $paging_info['curr_url']) . '/page'.($paging_info['curr_page'] + 1); ?>' title='Page <?php echo ($paging_info['curr_page'] + 1); ?>'>Next</a>

        <a href='<?php echo str_replace('/page'.$paging_info['curr_page'], '', $paging_info['curr_url']) . '/page'.$paging_info['pages']; ?>' title='Page <?php echo $paging_info['pages']; ?>'>Last</a>

    <?php endif; ?>
</p>

Linnlinnaeus

Thank you so much for your example and all the instructions,I'm now trying to understand and learn it. – Khabarovsk

Glad to help. Obviously, I've just grabbed this from my code and altered it a little but the structure is there, for you to play with. – Linnlinnaeus

The problem with this kind of page navigation is that it can be hard to get to pages in the middle of a '...' range (at least by clicking). You might consider using the approach I describe in this question, which allows for easy access to any page in a few mouseclicks, even when there are 1000s of pages. – Monsignor

This was a great basis for me, thanks! A couple little tweaks got me exactly where I wanted to be. – Screwy

What is the user of curr_url and where we are passing it in function ? – Tanguay

Answer 2

$count_pages = ceil($total / $items_per_page);

Gayl

Answer 3

You will need to use a LIMIT clause in your sql statement. For example:

SELECT <column> 
  FROM <table>
    LIMIT 0, 5

Reference: SELECT Syntax

Giesser

Thanks for your reply.Is it this row?$strSQL .=" order by addedrec_ID DESC LIMIT $Page_Start , $Per_Page"; – Khabarovsk

Answer 4

I solved a very similar issue for someone else with a very similar script here: How to limit pages shown in pagination script

Also, I noticed a few other anomalies with your script (besides the unreadable formatting that I fixed). You should change all occurrences of $_SERVER[SCRIPT_NAME] to $_SERVER['SCRIPT_NAME']. In your script, for instance:

echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=...";

Would become:

echo " <a href ='{$_SERVER['SCRIPT_NAME']}?Page=...";

Debra

Answer 5

my code always show at least 9 pages in case of total pages>9 whatever you are on page 1,2,3

<ul class="pagination pagination-sm" style="align:center">
    <li><a href="./?page=1"><</span></a></li>
    <?php
    if ($current_page <4){
        if ($total_pages<9)
        {
            for($i = 1; $i <=  $total_pages; $i++)
            {

                if ($i == $current_page)
                {
                    echo '<li class="active"><a href="#">'; echo $i.'</a></li>'."\n";
                } else {
                    echo ' <li><a href="./?page='.$i.'">'; echo $i.'</a></li>'."\n";
                }
            }
        }
        else
        {
            for($i = 1; $i <=  9; $i++)
            {

                if ($i == $current_page)
                {
                    echo '<li class="active"><a href="#">'; echo $i.'</a></li>'."\n";
                } else {
                    echo ' <li><a href="./?trang='.$i.'">'; echo $i.'</a></li>'."\n";
                }
            }
        }
    }
    elseif ($current_page >$total_pages-4)
    {
        if ($total_pages<9)
        {
            for($i = 1; $i <=  $total_pages; $i++){

                if ($i == $current_page){
                    echo '<li class="active"><a href="#">'; echo $i.'</a></li>'."\n";
                } else {
                    echo ' <li><a href="./?page='.$i.'">'; echo $i.'</a></li>'."\n";
                }
            }
        }else{
            for($i = $total_pages-8; $i <=  $total_pages; $i++){

                if ($i == $current_page){
                    echo '<li class="active"><a href="#">'; echo $i.'</a></li>'."\n";
                } else {
                    echo ' <li><a href="./?page='.$i.'">'; echo $i.'</a></li>'."\n";
                }
            }
        }

    }else{
        for($i = max(1, $current_page - 4); $i <= min($current_page + 4, $total_pages); $i++){

            if ($i == $current_page){
                echo '<li class="active"><a href="#">'; echo $i.'</a></li>'."\n";
            } else {
                echo ' <li><a href="./?trang='.$i.'">'; echo $i.'</a></li>'."\n";
            }
        }
    }
    ?>
    <li>
        <a href="./?trang=<?php echo $total_pages ?>">></a>
    </li>
</ul>

Tanbark

Answer 6

make it simple, here is code:

<?php       
    //put this code on the begining of the page
    $page=$_GET['page']; //Getting page number from url | example: www.mydomain.com/index.php?page=3
    if(!is_numeric($page)) $page=1; //If there is no page number specified we set number 1
$queryCount = "SELECT * FROM table "; //select from db
$query = $DB->query( $queryCount);
    $num = mysql_num_rows ($query); //count num rows
    $per_page=5; //default 5 results per page
$start=$per_page*($page-1); //start for select on next page (page2, 3, 4,5)
$end=min($num,$page*$per_page); //end


//here is select for your results. Be careful for LIMIT in the select!
$query = "SELECT * FROM page LIMIT $start, $per_page ";


//page bottom, where you want to put your numbers
$pages=ceil($num/$per_page);
  for($s=1; $s<=$pages; $s++)
  {
    if($s==$page)
      $numPage  .= "[$s] ";
    else
      $numPage  .= "<a href='index.php?page=$s'>$s</a> ";
  }

echo $numPage;

?>

I didn't test it, but I think it must work :-)

Cind regards, Ivan

Goshawk

Code works but this just simply prints all page numbers. The question is to limit page numbers to for example 10 page numbers only. if user is on page 12 then it should show for example 10 to 20 etc. – Indies

Answer 7

Purpose of this code is only to show definite (certain) number of pages in pagination

Get current page number from url and define $curr_pg_numb

Get number of total pages from mysql and define $total_pages

And here is code for pagination

$counter=0;
$init_num_to_left = 3;//initial number of pages to show to left from current page
$init_num_to_right = 4;

if( $curr_pg_numb - $init_num_to_left < 1 ){
$init_num_to_right = $init_num_to_right + ( 1 - ($curr_pg_numb - $init_num_to_left) );
$init_num_to_left = $curr_pg_numb - 1;
}
else if( $curr_pg_numb + $init_num_to_right > $total_pages ){
$init_num_to_left = $init_num_to_left + ( ($curr_pg_numb + $init_num_to_right) - $total_pages );
$init_num_to_right = ($curr_pg_numb + $init_num_to_right) - $total_pages;
}

while($counter <= $total_pages ) {
  if ( $counter > 0 ){
    if ($curr_pg_numb==$counter){
    echo '<strong> '. ( htmlspecialchars($curr_pg_numb , ENT_QUOTES, "UTF-8") + 0 ). '</strong> ';
    }
    else {
    if ( $counter >= ($curr_pg_numb - $init_num_to_left ) and $counter <= ($curr_pg_numb + $init_num_to_right ) ) {
    echo '<a href="?cpn='. htmlspecialchars(($counter), ENT_QUOTES, "UTF-8"). '" style="margin:5px;">'. htmlspecialchars(($counter), ENT_QUOTES, "UTF-8"). '</a>';
    }
    }//else {
  }//if ( $counter > 0 ){
$counter++;
}//while($counter <= $total_pages ) {

Orthodox