Saturday, July 20, 2013

Basic pagination with Mysql, PHP

Have you ever noticed why blogger or wordpress or any other blogging platform have added a 'Next Page' button or you ever noticed that why facebook directly won't shows all the posts as well as twitter won't load the tweets ? The reason is: while loading tweets or loading status or articles from database developers loop over the database again and again and if their are many thousands or hundreds of status, tweets etc.. than the page get slow/crashes. That's the reason why they use pagination. By the way facebook and twitter loads their feed using Ajax.


Let's first connect to our database:
PHP: Connecting to mysql database.
<?php
    $db_host = 'localhost'; // your mysql host
    $db_user = 'ashwin1999'; // your mysql user name
    $db_pass = ''; // password not set
    $db_name = 'pagination';
    mysql_connect($db_host, $db_user, $db_pass);
    mysql_select_db($db_name);
?>

After connecting let's move on adding tables and columns. First we'll create a table called 'page' and then we'll create column which name will be 'data' just for example i'm adding this names. You can even use PHPmyadmin (PMA) to make tables columns. Also i have added numbers from 1-20. Refer below image to understand it properly.









Let's begin with PHP code blocks!
PHP: Pagination code
<?php
   @$p = $_GET["page"]; //Getting Page number

   $pages_query = mysql_query("SELECT COUNT(data) FROM page"); // Counting total rows
   if($p=="" || $p=="0" || $p>$pages_query) { //checking is p is set and greater than 0
   $p = 1; //if not set than setting it to 1
   }

   $per_page = 5; //Total data to display per page
   $pages = ceil(mysql_result($pages_query, 0) / $per_page); //dividing total rows with total data to
   display for example 20/10=2 so 2 pages

   $start = ($p - 1) * $per_page; // subtracting $p value with 1 and multiplying it with $per_page for                  example 2-1=1*10 = 10

   $query = mysql_query("SELECT data FROM page ORDER BY data ASC LIMIT $start, $per_page");      //Running our query

   while($fetch_data = mysql_fetch_array($query)) { //fetching data using array method
   echo $fetch_data["0"]."<br />"; //printing the data
   }

   for($a=1;$a<=$pages;$a++) { //using for to display number
        echo "<a href='?page=$a' class='page_link'>$a</a> "; //printing numbers also using link tags
   }
?>

It just looks hard but it's not hard, just need to do the subtract and divide part properly and the it's done.

6 comments:

  1. wrks cul
    y dnt u add live demo

    ReplyDelete
    Replies
    1. yet...i don't have a hosting....finding a solution for demo...will be back with a solution.

      Delete
  2. plz.. give me details of this code..!

    ReplyDelete
    Replies
    1. the code is above....you can copy and paste it :) to get more tutorials you can subscribe to my blog.. :)

      Delete
  3. that is awesome .....at last you give me the proper code...thanks allot dear....

    ReplyDelete

Your Comment Will Be Visible After Approval, Thanks !