Wednesday, February 12, 2014

Share It

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.


You may also like:

Beautiful contact form for websites 

Add Google plus follower widget to blogger 

Stylish sticky menu bar with social icons for footer 

 Make money with facebook 

Add contact form to your blogger blog free 

Get more visitors to your site with SEO

Remove facebook graph search

Trick to find fake accounts on facebook

Get free mobile balance 100% working trick in India only

Speed loading like button for blogger

Delete facebook account permanently 100% working trick

Trick to use facebook on slow internet

How to get more page likes on facebook

How to delete google search history

8 Tricks to reduce load time of your website

3 ways to make money online without investment

5 idiotic ways to promote your blog


I said before that this code is based on PHP and MySQL, hence this code reads the data from the database tables and displays it on our page with pagination links. Since the data is being retrieved from the tables in the database we need to use sql connection mysql_connect() and then we need select database using mysql_select_db(). Let us see briefly how to connect to database using these two functions in PHP.

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
}
?>
<!--creates links to display at bottom with previous, next and page numbers-->
                <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
}
?>
<!--creates links to display at bottom with previous, next and page numbers-->
                <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.

0 comments: