PHP Tutorial: Searching and Pagination

Comments 48 Standard

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 <form> around those input tags so we can use a submit input type to submit our form to the server:

<?php
require_once('database.php'); //connect to your database in this file

//display our search form
echo "<h2>Search Members</h2>
<form method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* 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:

<?php
require_once('database.php'); //connect to your database in this file

//display our search form
echo "<h2>Search Members</h2>
<form action='{$_SERVER['PHP_SELF']}' method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* Display Search Results Below Here
**/
//build our query based on what they entered in the form
$sql = "SELECT * FROM members WHERE 1=1";

if (isset($_POST['id']))
   $sql .= " AND id='" . mysql_real_escape_string($_POST['id']) . "'";

if (isset($_POST['username']))
   $sql .= " AND username='" . mysql_real_escape_string($_POST['username']) . "'";

if (isset($_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 "<br/>{$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 "<center>" . number_format($total_records) . " search results found</center>";
?>

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 "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") . "\">";

        echo "$page ";

        if ($page != $current_page_number)
            echo "</a>";

        $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:

<?php
require_once('database.php'); //connect to your database in this file
define("NUMBER_PER_PAGE", 5); //number of records per page of the search 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 "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") . "\">";

        echo "$page ";

        if ($page != $current_page_number)
            echo "</a>";

        $page++;
    }
}

echo "<h2>Search Members</h2>
<form action='{$_SERVER['PHP_SELF']}' method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* 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 = isset($_POST['id']) ? $_POST['id'] : isset($_GET['id']) ? $_GET['id'] : null;
$username = isset($_POST['username']) ? $_POST['username'] : isset($_GET['username']) ? $_GET['username'] : null;
$email = isset($_POST['email']) ? $_POST['email'] : isset($_GET['email']) ? $_GET['email'] : null;

$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 "<br/>{$record['id']}) " . stripslashes($record['username']) . " - {$record['email']}";

echo "<center>" . number_format($total_records) . " search results found</center>";

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

Advertisements

48 thoughts on “PHP Tutorial: Searching and Pagination

  1. 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

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

  3. 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. 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?

  5. Add two input fields, one for the start date and another for the end date. Then when you’re generating your search results you can do this:

    if (isset($_POST['start_date']) && !isset($_POST['end_date'])) //they only entered a start date
     $sql .= " AND start_date >= '" . mysql_real_escape_string($_POST['start_date']) . "'";
    else if (isset($_POST['start_date']) && isset($_POST['end_date'])) //they entered a start and end date
     $sql .= " AND start_date >= '" . mysql_real_escape_string($_POST['start_date']) . "' AND end_date <= '" . mysql_real_escape_string($_POST['end_date']) . "'";
    else if (!isset($_POST['start_date']) && isset($_POST['end_date'])) //they only entered an end date
     $sql .= " AND end_date <= '" . mysql_real_escape_string($_POST['end_date']) . "'";
    
  6. i m new in php and i m using this code in my project.But i m facing the prob.when i m run this code on localhost i m getting some errors like Undefined index: id in C:\wamp\www\nikita_new\test25.php on line 112
    Undefined index: id in C:\wamp\www\nikita_new\test25.php on line 112
    Notice: Undefined index: username in C:\wamp\www\nikita_new\test25.php on line 113
    Notice: Undefined index: username in C:\wamp\www\nikita_new\test25.php on line 113
    Notice: Undefined index: email in C:\wamp\www\nikita_new\test25.php on line 113
    Notice: Undefined index: email in C:\wamp\www\nikita_new\test25.php on line 113

    i dont know how can i solve this error.
    plz help me for this
    thanks

  7. That’s because of the error reporting levels you have turned on in your php.ini file. You can fix the notices by doing an isset around the variables that it’s giving you notices for:

    if (isset($_POST['id']))
       $sql .= " AND id='" . mysql_real_escape_string($_POST['id']) . "'";
    
    if (isset($_POST['username']))
       $sql .= " AND username='" . mysql_real_escape_string($_POST['username']) . "'";
    
    if (isset($_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']}";
  8. I’m facing the same as Annu said the Notice: Undefined index: cust_id in C:\wamp\www\try\Search and Pagination\searchcustomer.php on line xxx , any solution ? It only happen during the 1st start run,if you refresh the page, the error message will gone.

  9. Hi Jade, how to get the last page as a default first page (order by Descending) ?Kindly advise, Thanks

  10. You can’t get the last page as the first page unless you explicitly set the page number when the pagination loads.. What you can do is order by the data you’re looking through. So if you wanted to order by last name you can add order by last_name DESC to the end of your query.

  11. I did change this –> $sql = “SELECT * FROM members WHERE 1=1”; to
    $sql = “SELECT * FROM members ORDER BY id DESC”;
    It work but it can’t do another searching, there is an error shown :
    cannot run the query because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND username = ‘chew choon giap’ LIMIT 0, 5′ at line 1.
    Kindly advise.

  12. That’s because the query is being dynamically created. You need to append your ORDER BY id DESC after the script goes through all of the logic to build the query and before it runs the query.

  13. Before $loop = mysql_query($sql) or die (‘cannot run the query because: ‘ . mysql_error()); add $sql .= ” ORDER BY id DESC”;

  14. $sql .= ” ORDER BY id DESC”;
    $loop = mysql_query($sql)
    or die (‘cannot run the query because: ‘ . mysql_error());
    But still have error.

  15. cannot run the query because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER BY cust_id DESC’ at line 1

  16. cannot run the query because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER BY id DESC’ at line 1

  17. I Love your work !, i am new to php, i usually code other language (foxpro), but there is one problem that i found in the function. if i try to search a name and the name is located under a page higher than current, it can found it, but if i go to a page #, and search a name in previous page than current, the page return nothing.

  18. Make sure you’re not still passing the page number when you start a new search. The page numbers are only necessary for the search results.

  19. You need to have more than $NUM_PER_PAGE records in your database in order to get next page results. Also make sure you’re passing the ?page=# in your next page link

  20. hi, i see page all the page numbers eventhough i have only 1 output. when i click page 2, it shows me all my users

  21. Hi Jade,
    I read your post regards this Date Range code shown below,

    if ($_POST[‘start_date’] && !$_POST[‘end_date’]) //they only entered a start date
    $sql .= ” AND start_date >= ‘” . mysql_real_escape_string($_POST[‘start_date’]) . “‘”;
    else if ($_POST[‘start_date’] && $_POST[‘end_date’]) //they entered a start and end date
    $sql .= ” AND start_date >= ‘” . mysql_real_escape_string($_POST[‘start_date’]) . “‘ AND end_date <= '" . mysql_real_escape_string($_POST['end_date']) . "'";
    else if (!$_POST['start_date'] && $_POST['end_date']) //they only entered an end date
    $sql .= " AND end_date <= '" . mysql_real_escape_string($_POST['end_date']) . "'";

    How to do the isset POST code ? Kindly advise.

  22. Oh…I means must these variable before run SQL, exapmle as below
    $id = isset($_POST[‘id’]) ? $_POST[‘id’] : isset($_GET[‘id’]) ? $_GET[‘id’] : null;

  23. Yes, if you don’t put them before the SQL then they won’t be defined when the query tries to run.

  24. So , is it correct if the code as follow
    $start_date = isset($_POST[‘start_date’]) ? $_POST[‘start_date’] : isset($_GET[‘start_date’]) ? $_GET[‘start_date’] : null;
    $end_date = isset($_POST[‘end_date’]) ? $_POST[‘end_date’] : isset($_GET[‘end_date’]) ? $_GET[‘end_date’] : null;

  25. thank you for a great tutorial! The download source link for the files is not working – is there another way to get the files needed for this tutorial?
    thanks,
    Helen.

  26. halo bro…this it great top tutorial..

    but i was ask, how to change style and color in pagination CSS ? and than when change page of pagination ?

    thank before it !

  27. why if i’am searching and searching (on second process after first search) can’t respon ?

    I think, the get on page not refresh on new filter search. how to fix it ? thank before..hope you always helth

  28. What do you want to change the style of? The page numbers? What do you want to change them to?

  29. It sounds like your database is not responding, it could also be that your search has run into an error. I would check your server’s error logs or turn error logs on in your your php code.

  30. I think if i’am combinate style of pagination (e.g bootsrap css style pagination) for great paging…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s