PHP and MySQL Search/Browse Pagination Tutorial

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.