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
"; /** * 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"; /** * 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 "
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
";
/**
* 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 "
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
Cool !
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
I host all of my own websites, I offer hosting for online games if you’re interested.
Great….!!! 🙂
But if i want to show prev and next button with only 5 pages out of 50…and hide remaining..
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 >"; }Thank jade…. :-)…..
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?
put
if (isset($_POST['submit']))before each of the pagination function calls.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 !
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’]).
Hi Jade, how do I add the date range searching ?
add input fields for a to and from date with the format YYYY-MM-DD, then do something like this:
if (isset($_POST['to_date'])) { $sql .= ' AND `date` >= "' . mysql_real_escape_string($_POST['to_date']) . '"; }if (isset($_POST['from_date'])) { $sql .= ' AND `date` < = "' . mysql_real_escape_string($_POST['from_date']) . '"; }
I did follow your suggestion but fail, cannot search data.
What error are you getting?
No result shown.
How do I show the script whether I have done correctly ?