W3C validation Service online

Simple PHP MYSQL Pagination

simple-php-mysql-pagination

Pagination can get complicated depending on the size of records in the database, especially when you want to group number of links, display next/previous links etc. In this tutorial we’ll focus on creating simple PHP MYSQL pagination.

Let’s Create MYSQL Database

An SQL file is included in the complete script file attached. Please create a MYSQL database first, then import .sql file. (This is only for this tutorial purpose but you can use your own database as well if you have already created.)

Database Connection:

Let’s start with database connection file to store our MySql database username and password, and also to connect to MySQL using PHP mysqli_connect(). If you notice we are using PHP mysqli_connect() here, not regular mysql_connect(), i = stands for improved extension, developed to take advantage of new features found in newer MySQL systems. PHP encourages everyone to use mysqli_* instead of regular mysql_* extensions, which will be totally removed in the future PHP versions.

Create connection.php file and put that php code (provided below) in it.


$db_username = 'root'; // Your MYSQL Username.
$db_password = ''; // Your MYSQL Password.
$db_name = 'database_name_here'; // Your Database name.
$db_host = 'localhost';
 
$conDB = mysqli_connect($db_host, $db_username, $db_password,$db_name)or die('Error: Could not connect to database.');

Pagination function:

Let’s create pagination function and store it in your functions.php file. (We’re using functions.php file to store this function. We prefer this method because that way, we can easily add more helpfull functions to our file later.)

function pagination($query,$per_page=10,$page=1,$url='?'){   
    global $conDB; 
    $query = "SELECT COUNT(*) as `num` FROM {$query}";
    $row = mysqli_fetch_array(mysqli_query($conDB,$query));
    $total = $row['num'];
    $adjacents = "2"; 
     
    $prevlabel = "‹ Prev";
    $nextlabel = "Next ›";
	$lastlabel = "Last ››";
     
    $page = ($page == 0 ? 1 : $page);  
    $start = ($page - 1) * $per_page;                               
     
    $prev = $page - 1;                          
    $next = $page + 1;
     
    $lastpage = ceil($total/$per_page);
     
    $lpm1 = $lastpage - 1; // //last page minus 1
     
    $pagination = "";
    if($lastpage > 1){   
        $pagination .= "<ul class='pagination'>";
        $pagination .= "<li class='page_info'>Page {$page} of {$lastpage}</li>";
             
            if ($page > 1) $pagination.= "<li><a href='{$url}page={$prev}'>{$prevlabel}</a></li>";
             
        if ($lastpage < 7 + ($adjacents * 2)){   
            for ($counter = 1; $counter <= $lastpage; $counter++){
                if ($counter == $page)
                    $pagination.= "<li><a class='current'>{$counter}</a></li>";
                else
                    $pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";                    
            }
         
        } elseif($lastpage > 5 + ($adjacents * 2)){
             
            if($page < 1 + ($adjacents * 2)) {
                 
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++){
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>{$counter}</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";                    
                }
                $pagination.= "<li class='dot'>...</li>";
                $pagination.= "<li><a href='{$url}page={$lpm1}'>{$lpm1}</a></li>";
                $pagination.= "<li><a href='{$url}page={$lastpage}'>{$lastpage}</a></li>";  
                     
            } elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
                 
                $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                $pagination.= "<li class='dot'>...</li>";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++) {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>{$counter}</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";                    
                }
                $pagination.= "<li class='dot'>..</li>";
                $pagination.= "<li><a href='{$url}page={$lpm1}'>{$lpm1}</a></li>";
                $pagination.= "<li><a href='{$url}page={$lastpage}'>{$lastpage}</a></li>";      
                 
            } else {
                 
                $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                $pagination.= "<li class='dot'>..</li>";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++) {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>{$counter}</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";                    
                }
            }
        }
         
            if ($page < $counter - 1) {
				$pagination.= "<li><a href='{$url}page={$next}'>{$nextlabel}</a></li>";
				$pagination.= "<li><a href='{$url}page=$lastpage'>{$lastlabel}</a></li>";
			}
         
        $pagination.= "</ul>";        
    }
     
    return $pagination;
}

Displaying Database Records with pagination:

include_once('connection.php');
include_once('functions.php');

$page = (int)(!isset($_GET["page"]) ? 1 : $_GET["page"]);
if ($page <= 0) $page = 1;

$per_page = 10; // Set how many records do you want to display per page.

$startpoint = ($page * $per_page) - $per_page;

$statement = "`records` ORDER BY `id` ASC"; // Change `records` according to your table name.
 
$results = mysqli_query($conDB,"SELECT * FROM {$statement} LIMIT {$startpoint} , {$per_page}");

if (mysqli_num_rows($results) != 0) {
    
	// displaying records.
    while ($row = mysqli_fetch_array($results)) {
        echo $row&#91;'name'&#93; . '<br>';
    }
 
} else {
     echo "No records are found.";
}

 // displaying paginaiton.
echo pagination($statement,$per_page,$page,$url='?');

Simple CSS:

ul.pagination {
	text-align:center;
	color:#829994;
}
ul.pagination li {
	display:inline;
	padding:0 3px;
}
ul.pagination a {
	color:#0d7963;
	display:inline-block;
	padding:5px 10px;
	border:1px solid #cde0dc;
	text-decoration:none;
}
ul.pagination a:hover, 
ul.pagination a.current {
	background:#0d7963;
	color:#fff; 
}

Complete Pagination script

We just created a Simple PHP MYSQL Pagination function. You can download the complete script from right sidebar. Click on Download Attachment button.

Functions Used:

  • 很好

  • Santiago

    how are you, i’m having this bug using bootstrap with the ‘dots’ while having many results

    http://s11.postimg.org/ownq0irrn/Captura_de_pantalla_2014_08_26_a_la_s_19_55_29.png

    • otallu

      Good. Looks like styling issue. Please Try adding different class name here in pagination function”…”. Hope That will work.

      • I have this same issue. Changing the class does not work. It moves the dots to end.

  • Chanthan

    function pagination($query,$per_page=10,$page=1,$url=’?’)

    I don’t know about $url=’?’, can you explain me?

    really thank you for teach…me…..

    • otallu

      That $url variable will be included in each link: ” href='{$url}page=1′ “. So, if you want to pass variables through page URL, you can do it like this.

      Before:
      pagination($query,$per_page=10,$page=1,$url=’?’){
      output: index.php?page=1
      After:
      pagination($query,$per_page=10,$page=1,$url=’?order=ASC&’){
      output: index.php?order=ASC&page=1

      • Chanthan

        Sorry can you sent incloud database and code php for me by email:maochanthan@gmail.com
        because this project I do for my assignment, and I am a student in Cambodia but I just study years two ….thank you for teach me a lot

        • otallu

          email sent!

    • NAVEED KHAN

      thank u so much sir
      its working

  • Chanthan

    I why pagination show on data display, can you tell me?

  • Chanthan

    sorry can you help me, when I click to page 2 it isn’t show data, I don’t know why?

    • Chanthan

      This is page 2 when I click

  • Chanthan

    If don’t don’t have page root is work good but if I get it to page root it don’t work

  • Sharif

    Hi

    Excellent job. I’m using it perfectly.

    But when i’m using GET value from the url everything used to remove and just contain ?page=1.
    So i’m trying to use $url=’?’ where $url= “current url”..it’s working well.
    but it’s repeating &page= more and more.like “&page=2&page=1&page=2&page=1”

    how to solve it?

    thanks

    • Chanthan

      Problem the same me, but when I require to page Admin and then when I click to page 2 or Next it’s don’t show data.

      • Chanthan

        your project is already …

    • otallu

      I have attached the complete working script now.. Please download it from the right sidebar and follow it line by line. Please check and see where did you go wrong. Also check $url should be ‘?’ if you’re not passing any data through URL otherwise it should be set as ‘?order=asc&’ or any other data you want to pass …. and ‘page=’ shouldn’t be included.

      • Sharif

        page= i did not include.when i’m searching data with GET method so the url is dynamic. then how i’ll set $url variable?

        I’m using $_SERVER[“REQUEST_URI”] that’s why every time ‘page=’ is repeating.any solution for dynamic url which containing GET method?

        • otallu

          You shouldn’t use $_SERVER[“REQUEST_URI”] for that case. You can use simple Get variables like this example:
          if (isset($_GET[‘order’])) {
          $url = “?order=” . $_GET[‘order’] . “&”;
          } else {
          $url = “?”;
          }

          • Sharif

            thanks for your reply.it’s working great.

            thanks again

          • asif

            Sorry dear i found problem can you please help me out i want to pass variables in $ulr=’?’

            function pagination($query, $per_page = 10,$page = 1, $url = ‘?id=”$_GET[“id”]”&projectname=”$_GET[“projectname”]”&detail=”$_GET[“detail”]”&’){

            i want this to done but variables cant get the value

  • disqus_mz7FxM4gzz

    Error in downloading..404 Error…

    • otallu

      Sorry, Link is fixed now.

  • Thanks! That helped me a lot!

  • asif

    Sorry dear i found problem can you please help me out i want to pass variables in $ulr=’?’

    function pagination($query, $per_page = 10,$page = 1, $url = ‘?id=”$_GET[“id”]”&projectname=”$_GET[“projectname”]”&detail=”$_GET[“detail”]”&’){

    i want this to done but variables cant get the value

  • subs

    Hi..

    How do I display the $lastpage value?

    Thanks

  • DealBeal.in

    there is one prob, if u keep $total = 100 and $per_page = 3 then check how the pagination looks. It does not look good.

  • DLively

    Thanks!! With a little modifications I have it fitting my project now!

  • Pingback: Pagination data with php « Blog — organiser()

  • Abhijeet

    Thank you so much for this script.
    I m satisfy.

  • CodeWiz

    I have 3 files viz index.php, search.php and functions.php

    In search.php I am fetching the search query entered by user using $searchInput=$_POST[‘search’];

    Now, when I click on 2nd record, I get the following error

    Notice: Undefined index: search in C:wampwwwmykart_1.1search.php on line 24

    i.e. when the search.php is loaded for the second time, it shows that $_POST[‘search’] has an undefined index search.

    Can anyone please help me out.

    P.S I also tried writing this code snippet but it still shows the error for ‘search’

    if (isset($searchInput)) {
    $url = “?search=” . $searchInput . “&”;
    //$searchInput=$_POST[‘search’];
    } else {
    $searchInput=$_POST[‘search’];
    $url = “?”;
    }

  • Tom
  • Leon Goldschagg

    Hi sorry if I am late to the party. I have used a search function. Which workd well eith the pag code – BUT, when moving to next page it loses the search criteria and produces all records. Please help.

    // Set how many records do you want to display per page.
    $per_page = 5;
    $startpoint = ($page * $per_page) – $per_page;

    //**********************************************
    //* Get Status
    //**********************************************
    if (isset($_POST[‘statuscode’]))
    {
    $statuscode = $_POST[‘statuscode’]; //Set status code variable to ur choice

    if (empty($statuscode)) //If no selection made set to ALL
    {
    $statuscode = ‘ALL’;
    }

    }
    else
    {
    $statuscode = ‘ALL’;
    }

    //========================================================
    //* SELECT from table and display Results
    //**********************************************

    $sql_statement = “SELECT * “;
    $sql_statement .= “FROM vancancies “;

    if ($statuscode != ‘ALL’)
    {

    $statement = “`vancancies`WHERE `registeredclient_id`=’$_id’ AND vacstatus = ‘”.$statuscode.”‘ ORDER BY `vacstartdate` ASC”;
    $results = mysql_query(“SELECT * FROM {$statement} LIMIT {$startpoint} , {$per_page}”);

    }

    else

    {

    $statement = “`vancancies` WHERE `registeredclient_id`=’$_id’ ORDER BY `vacstartdate` ASC”;
    $results = mysql_query(“SELECT * FROM {$statement} LIMIT {$startpoint} , {$per_page}”);

    }

    $sql = mysql_query($sql_statement);
    //==========================================================

    if (mysql_num_rows($results) != 0)
    {

    // displaying records.
    while ($row = mysql_fetch_array($results))
    {

    $i=0;

    {

    if($i%2==0)
    $classname=”evenRow”;
    else
    $classname=”oddRow”;
    ?>

    //<tr class="”>

    <input type="checkbox" name="jobs[]" value="” >

    R

    <?php echo ' . ‘>Edit

    • Mwine A Melchisedec

      Hello Leon, am facing the same situation, After filtration, any click onto the page numbers link takes you back to the original pagination. Did you find an answer?

  • zyrocs

    Thanks a lot

  • raghav

    Good Explanation of all the necessary steps there is also an easy and short tutorial of Pagination using PHP and MySQL on TalkerCode.com http://talkerscode.com/webtricks/simple%20and%20best%20pagination%20with%20PHP,MySQL.php

  • 9t9sol.com

    did a great job ..
    i realy need it ..
    its easy, reliable and fast as wel as in mysqli that is the awesome part 😛

  • Ben Dada

    thanks it works perfectly

  • Moiz Arif

    Thank You Very Much…
    You solve my big Problem…

  • isoftec

    You have not mentioned .dot styling in simple css block ??

  • biniyam

    sorry ma brother could you send me the whole code using MySQL since i am using this until now.

    Thanks for your swift response!

  • Iman Yasmin

    Kind of confuse by the $query and $statement. Could you help me with mine?
    Here:
    $sql = odbc_exec($conn,’SELECT UserId,UserNm,Agency FROM dtUser AS A LEFT JOIN dtAgency AS B WHERE A.AgencyCd = B.AgencyCd UNION SELECT TOP 5 UserId,UserNm,Agency FROM dtUser AS A LEFT JOIN dtAgency AS B WHERE A.AgencyCd IS NULL’);