PHP Tutorial: Searching and Pagination

In the work that I’ve done for clients and just in browsing the web I’ve seen a fair amount of strange pagination scripts out there. If you’re looking for a simple pagination solution then you’ve found the right tutorial. This tutorial assumes you know PHP basics, SQL basics, and you can display results from a MySQL query result set.

1. Build The Search Page

In order for this to work you need to create a simple search page. In this example we’ll use a simple member’s table. The member’s table has three fields: member id (key, auto_increment), username (varchar 15) and email address (varchar 25). So our create table SQL looks like this:

CREATE TABLE members (
 id INT NOT NULL AUTO_INCREMENT,
 username VARCHAR(15) NOT NULL,
 email VARCHAR(25) NOT NULL,
 PRIMARY KEY (id)
);

Now that we have our table we can build our PHP search page. We want to be able to search all three fields of our member’s table so we’ll need three text input fields. We wrap a

around those input tags so we can use a submit input type to submit our form to the server:

Search Members

    

ID:

    

Username:

    

Email:

    

             

"; /** * We'll display our search results down here in the next step! **/ ?>

2. Build The Search Query

Now that we have our search form we need to create a query that will generate the results we’re looking for. Inside of our $_POST submit if statement we want to build a query based on which search values have been filled in. Our updated PHP file will look like this:

Search Members
    

ID:

    

Username:

    

Email:

    

             

"; /** * Display Search Results Below Here **/ //build our query based on what they entered in the form $sql = "SELECT * FROM members WHERE 1=1"; if ($_POST['id'])    $sql .= " AND id='" . mysql_real_escape_string($_POST['id']) . "'"; if ($_POST['username'])    $sql .= " AND username='" . mysql_real_escape_string($_POST['username']) . "'"; if ($_POST['email'])    $sql .= " AND id='" . mysql_real_escape_string($_POST['email']) . "'"; $loop = mysql_query($sql)    or die ('cannot run the query because: ' . mysql_error()); while ($record = mysql_fetch_assoc($loop))    echo "
{$record['id']}) " . stripslashes($record['username']) . " - {$record['email']}"; //this gets the total number of records returned by our query $total_records = mysql_num_rows(mysql_query($sql)); echo "
" . number_format($total_records) . " search results found
"; ?>

3. The Pagination Function

In the previous step we created the form to pull up all the results but if our member’s table has thousands of records in it our browser can timeout and the loading times can be really slow trying to display a large list of members. To remedy that we add pagination so only a few records appear on the page at a time. Our function will show the page numbers so we can easily add them to multiple parts of the search results (ie before and after the results):

/****
* Purpose: paginate a result set
* Precondition: current page, total records, extra variables to pass in the page string
* Postcondition: pagination is displayed
****/
function pagination($current_page_number, $total_records_found, $query_string = null)
{
    $page = 1;

    echo "Page: ";

    for ($total_pages = ($total_records_found/NUMBER_PER_PAGE); $total_pages > 0; $total_pages--)
    {
        if ($page != $current_page_number)
            echo "";

        echo "$page ";

        if ($page != $current_page_number)
            echo "";

        $page++;
    }
}

5. The Completed Script

Now that we have a function to display our page numbers we need to update our search to send the correct information to our function. We also need to update our query to only display a limited number of records. Since we’re going to use a variable in both our query and our function we’re going to define it as a global – that way both the function and the query can see it and we don’t have to pass it to our function or declare it twice. Mix that all together and here’s what you get:

 0; $total_pages--)
    {
        if ($page != $current_page_number)
            echo "";

        echo "$page ";

        if ($page != $current_page_number)
            echo "";

        $page++;
    }
}

echo "

Search Members

    

ID:

    

Username:

    

Email:

    

             

"; /** * Display Search Results Below Here **/ //load the current paginated page number $page = ($_GET['page']) ? $_GET['page'] : 1; $start = ($page-1) * NUMBER_PER_PAGE; /** * if we used the search form use those variables, otherwise look for * variables passed in the URL because someone clicked on a page number **/ $id = ($_POST['id']) ? $_POST['id'] : $_GET['id']; $username = ($_POST['username']) ? $_POST['username'] : $_GET['username']; $email = ($_POST['email']) ? $_POST['email'] : $_GET['email']; $sql = "SELECT * FROM members WHERE 1=1"; if ($id)     $sql .= " AND id='" . mysql_real_escape_string($id) . "'"; if ($username)     $sql .= " AND username='" . mysql_real_escape_string($username) . "'"; if ($email)     $sql .= " AND email='" . mysql_real_escape_string($email) . "'"; //this return the total number of records returned by our query $total_records = mysql_num_rows(mysql_query($sql)); //now we limit our query to the number of results we want per page $sql .= " LIMIT $start, " . NUMBER_PER_PAGE; /** * Next we display our pagination at the top of our search results * and we include the search words filled into our form so we can pass * this information to the page numbers. That way as they click from page * to page the query will pull up the correct results **/ pagination($page, $total_records, "id=$id&username=$username&email=$email"); $loop = mysql_query($sql)     or die ('cannot run the query because: ' . mysql_error()); while ($record = mysql_fetch_assoc($loop))     echo "
{$record['id']}) " . stripslashes($record['username']) . " - {$record['email']}"; echo "
" . number_format($total_records) . " search results found
"; pagination($page, $total_records, "id=$id&username=$username&email=$email"); ?>

Since the function makes things look a little messy I cleaned up my downloadable version so it includes the pagination function via a functions.php file. It also includes the SQL for creating the members table with some data in it. Happy searching!

Try the Working Version or Download The Source Code

You may also like...

10 Responses

  1. Shyam Singh says:

    Cool !

  2. more says:

    Great blog here! Also your website loads up very fast!
    What web host are you using? Can I get your affiliate link to your host?
    I wish my website loaded up as quickly as yours lol

  3. John says:

    Great….!!! 🙂
    But if i want to show prev and next button with only 5 pages out of 50…and hide remaining..

    • Jade says:

      Just change the pagination so that they only show a certain number of pages at a time.

      function pagination($page, $total, $query = null)
      {
      	$adjacents = 2;
      	$prev = $page - 1;
      	$next = $page + 1;
      	$lastpage = ceil($total/NUMBER_PER_PAGE);
      	$second_to_lastpage = $lastpage - 1;
      	$max_pages = 5;
      	
      	//make sure it has the & before the extras list
      	if ($query && substr($query, 0, 1) != "&")
      		$query = "&" . $query;
      
      	if ($page > 1) 
      		echo "<a href="?page=$prev$query">< Back</a> ";
      	else
      		echo "< Back";	
      		
      	//not enough pages to bother breaking it up
      	if ($lastpage <= $max_pages)
      	{	
      		for ($counter = 1; $counter <= $lastpage; $counter++)
      		{
      			if ($counter == $page)
      				echo "<span class="current">$counter</span> ";
      			else
      				echo "<a href="?page=$counter$query">$counter</a> ";					
      		}
      	}
      	
      	elseif ($lastpage > $max_pages)	//enough pages to hide some
      	{
      		//close to beginning; only hide later pages
      		if ($page < $max_pages)		
      		{
      			for ($counter = 1; $counter <= $max_pages; $counter++)
      			{
      				if ($counter == $page)
      					echo "<span class="current">$counter</span> ";
      				else
      					echo "<a href="?page=$counter$query">$counter</a> ";					
      			}
      		
      			echo "... <a href="?$query&page=$second_to_lastpage">$second_to_lastpage</a>
      				<a href="?page=$lastpage$query">$lastpage</a> ";		
      		}
      		elseif ($page >= $max_pages && ($page + $adjacents) < $lastpage)
      		{
      			
      			echo "<a href="?page=1">1</a>... ";
      			
      			for ($counter = $page - $adjacents; $counter <= ($page + $adjacents); $counter++)
      			{
      				if ($counter == $page)
      					echo "<span class="current">$counter</span> ";
      				else
      					echo "<a href="?page=$counter$query">$counter</a> ";					
      			}
      			
      			echo "... <a href="?page=$lastpage$query">$lastpage</a> ";		
      		}
      		//close to end; only hide early pages
      		else
      		{
      			echo "<a href="?page=1$query">1</a> 
      				<a href="?page=2$query">2</a>... ";
      				
      			for ($counter = $lastpage - $adjacents; $counter <= $lastpage; $counter++)
      			{
      				if ($counter == $page)
      					echo "<span class="current">$counter</span> ";
      				else
      					echo "<a href="?page=$counter$query">$counter</a> ";					
      			}
      		}
      	}
      
      	if ($page < $lastpage) 
      		echo "<a href="?page=$next$query">Next ></a>";
      	else
      		echo "Next >";
      }
  4. John says:

    Thank jade…. :-)…..

  5. Finally a pagination script that is explained and actually works! Thanks. One question, how do i stop it showing results on first view before the form has been submitted?

  6. ry46 says:

    Hallo bro…you’re the best…

    i was ask, how to file error (e.g PHP Notice: Undefined index: id in /home/filter_pagiantion.php on line 23) in :

    if ($id)
    $sql .= ” AND id='” . mysql_real_escape_string($id) . “‘”;

    if ($username)
    $sql .= ” AND username='” . mysql_real_escape_string($username) . “‘”;

    if ($email)
    $sql .= ” AND email='” . mysql_real_escape_string($email) . “‘”;

    thanks before it !

    • Jade says:

      That’s because this is older code. Change all the if statements to a $_POST instead. So if ($id) becomes if ($_POST[‘id’]) and if ($username) becomes if ($_POST[‘username’]).

Leave a Reply