Create simple pagination with PHP and MySQL

Wednesday, February 12, 2014

Create simple pagination with PHP and MySQL

Hi friends, todays tutorial is about creating a simple pagination using PHP and MySQL. I am going to explain you about creating a page with PREVIOUS and NEXT links at the bottom of your page with numberings.

Before looking at the code let's discuss about pagination concept. Pagination let's you create links that contains the data which is the continuation of the current item. Whenever there is large amount of data then it looks hard to load the entire data into a single page. Hence, we use the concept of pagination to allow the content to be split into parts and show only some part of the content per each page. This will give your website viewers a great experience and also has an advantage of speed loading of data, inorder to avoid the inconvenience.

Code for Database connection:

<?php
$dbhostname               = "host name here"; //replace with your host name usually localhostname
$dbusername               = "database user name"; //replace with your database user name.
$dbpassword              = "database password"; //replace with your password
$dbname                     = "database name"; //replace with your database name

$conn = mysql_connect($dbhostname, $dbusername, $dbpassword) or die ("Database connection failed");
mysql_select_db($dbname) or die ("Database selection failed");
?>

The above code is used for datavase connection. Now let's see the code that is required to generate pagination links.

Code to generate pagination:

<?php
function create_pagination($current_page, $total_pages)
{
    $create_page_links = '';
    // generates anchor tag for previous page if this is not first page
    if ($current_page > 1)
    {
      $create_page_links .= '<a href="current-page-name.php?page=' . ($current_page - 1) . '">PREV</a> ';
    }
    // generates page links with numbers till the loop ends
    for ($count = 1; $count <= $total_pages; $count++)
    {
      if ($current_page == $count)
    {
        $create_page_links .= ' ' . $count;
      }
      else
    {
         $create_page_links .= ' <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $count . '"> ' . $count . '</a>';
      }
    }

    // generates anchor tag link for next page if this is not last page
    if ($current_page < $total_pages) {
      $create_page_links .= ' <a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page + 1) . '">NEXT</a>';
    }
    return $create_page_links;
}

// Calculate pagination
$current_page = isset($_GET['page']) ? $_GET['page'] : 1;
$results_per_page =15; // displays number of results per single page replace 15 with number of pages you may want
$jump = (($current_page - 1) * $results_per_page); //3*2
$query = "select * FROM table_name order by id desc";//query to select from table
$result = mysql_query($query1);

$total = mysql_num_rows($result); //counts number of rows
$total_pages = ceil($total / $results_per_page);

$query = $query . " LIMIT $jump, $results_per_page";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
//your code here to retrieve rows
}
?>

                <div style="float:left; padding:20px">
                <?php
                if ($total_pages > 1)
                {
                  echo create_pagination($current_page, $total_pages);
                }
                ?>
                </div>

The above part of the code is to generate pagination links. I have clearly explained within the comments. So please look the code carefully and understand it. The above code is not included with database connection. Hence we can combine both the above codes together as shown below.

Pagination code with database connection:

<?php
$dbhostname               = "host name here"; //replace with your host name usually localhostname
$dbusername               = "database user name"; //replace with your database user name.
$dbpassword              = "database password"; //replace with your password
$dbname                     = "database name"; //replace with your database name

$conn = mysql_connect($dbhostname, $dbusername, $dbpassword) or die ("Database connection failed");
mysql_select_db($dbname) or die ("Database selection failed");
function create_pagination($current_page, $total_pages)
{
    $create_page_links = '';
    // generates anchor tag for previous page if this is not first page
    if ($current_page > 1)
    {
      $create_page_links .= '<a href="current-page-name.php?page=' . ($current_page - 1) . '">PREV</a> ';
    }
    // generates page links with numbers till the loop ends
    for ($count = 1; $count <= $total_pages; $count++)
    {
      if ($current_page == $count)
    {
        $create_page_links .= ' ' . $count;
      }
      else
    {
         $create_page_links .= ' <a href="' . $_SERVER['PHP_SELF'] . '?page=' . $count . '"> ' . $count . '</a>';
      }
    }

    // generates anchor tag link for next page if this is not last page
    if ($current_page < $total_pages) {
      $create_page_links .= ' <a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page + 1) . '">NEXT</a>';
    }
    return $create_page_links;
}

// Calculate pagination
$current_page = isset($_GET['page']) ? $_GET['page'] : 1;
$results_per_page =15; // displays number of results per single page replace 15 with number of pages you may want
$jump = (($current_page - 1) * $results_per_page); //3*2
$query = "select * FROM table_name order by id desc";//query to select from table
$result = mysql_query($query1);

$total = mysql_num_rows($result); //counts number of rows
$total_pages = ceil($total / $results_per_page);

$query = $query . " LIMIT $jump, $results_per_page";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{

//your code here to retrieve rows
}
?>

                <div style="float:left; padding:20px">
                <?php
                if ($total_pages > 1)
                {
                  echo create_pagination($current_page, $total_pages);
                }
                ?>
                </div>

The above code is the final one.Copy the code and use it wherever you want.

Note: Now just make these changes before using the code.

host name here //replace with your host name usually localhostname

database user name //replace with your database user name.

database password//replace with your password

database name //replace with your database name

current-page-name.php //Replace with the name of the page where this code is being used.

$results_per_page =15 //change 15 to whatever the number results you may want to display ina single page

//your code here to retrieve rows //Code to fetch the rows from a database table

Thanks for reading this articles.
If you still have any queries then please don't hesitate to comment below.

Author: Bhanu Chander Bathini (CEO, Tricks Town.)

Hey friends, I am Bhanu Chander a Web Designer/Developer, Content writer, Freelance SEO analyst and a blogger. I am pursuing my M.Tech in Computer Science and Engg. You can contact me on bhanu4funn@gmail.com for web design solutions at low cost with effective SEO.

Free Mobile Balance →

Labels: MySQL, PHP, Web Design, Web Development

Wednesday, February 12, 2014