Pagination in a website is very useful, especially in a search or browse page. In fact, almost all website with search pages utilize this technique. And no other websites other than search engines benefit from this. Here's simple implementation of
Google.
That being said, it's time you add this feature to you blog or website. Fortunately for us, search and browse pagination in PHP and MySQL in fairly easy, thanks to MySQL's
LIMIT keyword that you can use in
SELET queries.
In this tutorial, I'm going to use the database from this
post.
Start by setting the size or the number of rows of each page.
$page_size = 10;
Connect to the database and select the database to use.
$con = mysql_connect("localhost", "root", "root1")
or die("Cannot connect to the database. ".mysql_error().");
mysql_select_db("test");Using MySQL's
CEILING function, count the number of pages by dividing the rows with the
$page_size variable we declared earlier.
//COMPOSE QUERY
$sql = "SELECT
CEILING(count(1) / ".$page_size.") AS count
FROM employees";
$result = mysql_query($sql, $con);
if ($row = mysql_fetch_assoc($result))
{
$page_count = $row["count"];
}
Using the HTML
<a> tag, display the number of pages.
for ($i = 1; $i < $page_count + 1; $i++)
{
print "<a href='?page=".$i."'>".$i."</a> ";
}
Check if the
GET variable, page, is available and set the $limit variable.
if (isset($_GET["page"]))
{
$page_no = $_GET["page"];
$limit = ($page_no * $page_size) - $page_size;
}
else
{
$limit = 0;
}
Compose and execute the query and display the result.
$sql = "SELECT
CONCAT(first_name, ' ', last_name) AS name
FROM employees
LIMIT ".$limit .", ".$page_size;
$result = mysql_query($sql, $con);
print "<br/>";
while ($row = mysql_fetch_assoc($result))
{
print "<br/>".$row["name"];
}
Here's the complete code.
//SET THE ROWS FOR EVERY PAGE
$page_size = 10;
//CONNECT TO MYSQL DATABASE
$con = mysql_connect("localhost", "root", "crop")
or die("<p class='error-msg'>Cannot connect to the database. ".mysql_error()."</p>");
//SELECT A DATABASE
mysql_select_db("test");
//COMPOSE QUERY
$sql = "SELECT
CEILING(count(1) / ".$page_size.") AS count
FROM employees";
//EXECUTE THE QUERY
$result = mysql_query($sql, $con);
if ($row = mysql_fetch_assoc($result))
{
$page_count = $row["count"];
}
//DISPLAY A LINK TO THE NUMBER OF PAGES
for ($i = 1; $i < $page_count + 1; $i++)
{
print "<a href='?page=".$i."'>".$i."</a> ";
}
//CHECK IF PAGE VARIABLE IS AVAILABLE AND CALCULATE THE LIMIT
if (isset($_GET["page"]))
{
$page_no = $_GET["page"];
$limit = ($page_no * $page_size) - $page_size;
}
else
{
$limit = 0;
}
//COMPOSE QUERY
$sql = "SELECT
CONCAT(first_name, ' ', last_name) AS name
FROM employees
LIMIT ".$limit .", ".$page_size;
//EXECUTE THE QUERY
$result = mysql_query($sql, $con);
print "<br/>";
while ($row = mysql_fetch_assoc($result))
{
print "<br/>".$row["name"];
}
If you follow my example above, you should be able to see the result like this.
