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.
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.
It just looks hard but it's not hard, just need to do the subtract and divide part properly and the it's done.
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);
?>
$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.
Subscribe to:
Post Comments (Atom)
wrks cul
ReplyDeletey dnt u add live demo
yet...i don't have a hosting....finding a solution for demo...will be back with a solution.
Deleteplz.. give me details of this code..!
ReplyDeletethe code is above....you can copy and paste it :) to get more tutorials you can subscribe to my blog.. :)
Deletethat is awesome .....at last you give me the proper code...thanks allot dear....
ReplyDeleteThank you
ReplyDelete