|
|
|
|
|
|
| |
I see this asked allot on the forums and even though there are several ready made classes for this I decided to add a How To for this issue.
In this tutorial I will show how to take a MySQL table that describes a list of articles, search the table and show the search results with paging similar to how major search engines let you page between the SERPs (Search Engine Result Pages).
Create the table
The following table is a simple table that holds only the ID and the Title of the articles. These are the only fields that are relevant to our tutorial. I also included a dump you can use for testing the code.
|
|
--
-- Table structure for table 'Articles'
--
CREATE TABLE 'Articles' (
'ArticleID' int(11) NOT NULL,
'ArticleTitle' varchar(255) NOT NULL
) ENGINE=MyISAM
--
-- Dumping data for table 'Articles'
--
INSERT INTO Articles VALUES (1, 'XML Basics (part 1)');
INSERT INTO Articles VALUES (2, 'PHP 101 (part 14): Going To The Polls');
INSERT INTO Articles VALUES (3, 'User Authentication With patUser (part 3)');
INSERT INTO Articles VALUES (4, 'PHP 101 (part 12): Bugging Out');
INSERT INTO Articles VALUES (5, 'PHP 101 (part 13): The Trashman Cometh');
INSERT INTO Articles VALUES (6, 'User Authentication With patUser (part 2)');
INSERT INTO Articles VALUES (7, 'PHP 101 (part 11): Sinfully Simple');
INSERT INTO Articles VALUES (8, 'User Authentication With patUser (part 1)');
INSERT INTO Articles VALUES (9, 'Using Transactions In MySQL (part 2)');
INSERT INTO Articles VALUES (10, 'PHP 101 (part 10): A Session In The Cookie Jar');
INSERT INTO Articles VALUES (11, 'Using Adobe''s Flex Builder tool to connect a PHP backend to the front end');
INSERT INTO Articles VALUES (12, 'Using Transactions In MySQL (part 1)');
INSERT INTO Articles VALUES (13, 'PHP 101 (part 9): SQLite My Fire!');
INSERT INTO Articles VALUES (14, 'Using Adobe''s Flex Builder tool to connect to a PHP backend system.');
INSERT INTO Articles VALUES (15, 'Serializing XML With PHP');
INSERT INTO Articles VALUES (16, 'PHP 101 (part 8): Databases and Other Animals');
INSERT INTO Articles VALUES (17, 'Getting More Out Of Apache (part 2)');
INSERT INTO Articles VALUES (18, 'FPDF, a PHP class which allows to generate PDF without using the PDFlib library.');
INSERT INTO Articles VALUES (19, 'Getting More Out Of Apache (part 1)');
INSERT INTO Articles VALUES (20, 'PHP 101 (part 7): The Bear Necessities');
INSERT INTO Articles VALUES (21, 'Injecting XML Content Into Page Templates With patXMLRenderer (part 2)');
INSERT INTO Articles VALUES (22, 'PHP 101 (part 6): Function-ally Yours');
INSERT INTO Articles VALUES (23, 'Aspect-Oriented Programming and PHP');
INSERT INTO Articles VALUES (24, 'Injecting XML Content Into Page Templates With patXMLRenderer (part 1)');
INSERT INTO Articles VALUES (25, 'PHP 101 (part 5): Rank And File');
INSERT INTO Articles VALUES (26, 'Caching With PHP Cache_Lite');
INSERT INTO Articles VALUES (27, 'PHP 101 (part 4): The Food Factor');
INSERT INTO Articles VALUES (28, 'Doing More With phpMyAdmin (part 2)');
INSERT INTO Articles VALUES (29, 'PHP 101 (part 3): Looping The Loop');
INSERT INTO Articles VALUES (30, 'PHP 101 (part 2): Calling All Operators');
INSERT INTO Articles VALUES (31, 'Doing More With phpMyAdmin (part 1)');
INSERT INTO Articles VALUES (32, 'PHP 101 (part 1): Down The Rabbit Hole');
INSERT INTO Articles VALUES (33, 'Using PEAR Console_Getopt To Process The PHP Command Line');
INSERT INTO Articles VALUES (34, 'Send SMS Thru HTTP');
INSERT INTO Articles VALUES (35, 'File And Directory Manipulation In PHP (part 2)');
INSERT INTO Articles VALUES (36, 'Using Subqueries In MySQL (part 2)');
INSERT INTO Articles VALUES (37, 'Using Subqueries In MySQL (part 1)');
INSERT INTO Articles VALUES (38, 'File And Directory Manipulation In PHP (part 1)');
INSERT INTO Articles VALUES (39, 'Data, its presentation and user interface forms');
INSERT INTO Articles VALUES (40, 'Date Arithmetic With MySQL');
INSERT INTO Articles VALUES (41, 'Miles To Go Before I Sleep...');
INSERT INTO Articles VALUES (42, 'Web/database Information Flow');
INSERT INTO Articles VALUES (43, 'Structure Means Subqueries: Nesting SELECTs in MySQL 4.1');
INSERT INTO Articles VALUES (44, 'Preloading HTML Content with CSS');
INSERT INTO Articles VALUES (45, 'HTML, CSS and Tables: The Beauty of Data');
INSERT INTO Articles VALUES (46, 'Prepared Statements');
INSERT INTO Articles VALUES (47, 'Temporal Functionality');
INSERT INTO Articles VALUES (48, 'Unicode and Other Funny Characters');
INSERT INTO Articles VALUES (49, 'GIS and Spatial Extensions with MySQL');
INSERT INTO Articles VALUES (50, 'Zend Studio, Sourceforge And Windows Stop pulling out your hair');
INSERT INTO Articles VALUES (51, 'Access vs. MySQL');
INSERT INTO Articles VALUES (52, 'Introduction to using PHP 5.0 and SQLite');
INSERT INTO Articles VALUES (53, 'Introduction to Design Patterns Using PHP');
INSERT INTO Articles VALUES (54, 'Simple Dynamic Templating for Small Sites Using PHP');
INSERT INTO Articles VALUES (55, 'More PHP, XML and XSL for Wireless Content');
INSERT INTO Articles VALUES (56, 'Smarty Introduction');
INSERT INTO Articles VALUES (57, 'Coding Criteria: Sloppy VS. Clean');
INSERT INTO Articles VALUES (58, 'PHP5: Designing And Using Interfaces');
INSERT INTO Articles VALUES (59, 'PHP Classes And Objects: A Guide To Development');
INSERT INTO Articles VALUES (60, 'Using Control Structures in PHP');
INSERT INTO Articles VALUES (61, 'Working with Permissions in PHP, Part 1');
INSERT INTO Articles VALUES (62, 'Use PHP to Create Dynamic pdf Files');
INSERT INTO Articles VALUES (63, 'PHP CLI and Cron');
INSERT INTO Articles VALUES (64, 'What to Expect in PHP 5.0');
INSERT INTO Articles VALUES (65, 'User identification using cookies in PHP/MySQL');
INSERT INTO Articles VALUES (66, 'Creating a Secure PHP Login Script');
INSERT INTO Articles VALUES (67, 'Sockets and PHP');
INSERT INTO Articles VALUES (68, 'Generating One-Time URLs with PHP');
INSERT INTO Articles VALUES (69, 'A Test To See If You Write Sloppy Software');
INSERT INTO Articles VALUES (70, 'Working with Dates and Times in PHP');
INSERT INTO Articles VALUES (71, 'PHP for Beginners by a Beginner: Simple Login, Logout, and Session Handling');
INSERT INTO Articles VALUES (72, 'Abstracting Oracle Connectivity with PHP/OCI8');
INSERT INTO Articles VALUES (73, 'Converting XML Into a PHP Data Structure');
INSERT INTO Articles VALUES (74, 'Dynamic Thumbnail Generation');
INSERT INTO Articles VALUES (75, 'Writing A Port Scanner In PHP');
INSERT INTO Articles VALUES (76, 'Multiple pages of data from a text file');
INSERT INTO Articles VALUES (77, 'Using Multiple Pages for Navigation');
INSERT INTO Articles VALUES (78, 'How TO Install PHP, Apache and MySQL on Linux / Unix');
INSERT INTO Articles VALUES (79, 'Building a PHP Style Switcher');
INSERT INTO Articles VALUES (80, 'Protecting PHP Scripts with HTTP Authorization');
INSERT INTO Articles VALUES (81, 'PHP/MySQL News with Comments');
INSERT INTO Articles VALUES (82, 'Database Abstraction with PEAR');
INSERT INTO Articles VALUES (83, 'Download Counting with Apache and PHP');
INSERT INTO Articles VALUES (84, 'Installing Apache Web Server + PHP 4 on Linux');
INSERT INTO Articles VALUES (85, 'PHP Debugging Tutorial');
INSERT INTO Articles VALUES (86, 'Overriding the php.ini with no access!');
INSERT INTO Articles VALUES (87, 'Mini-Chat Tutorial');
INSERT INTO Articles VALUES (88, 'Multicolumn Output from a Database with PHP');
INSERT INTO Articles VALUES (89, 'Show the Number of Users Online');
INSERT INTO Articles VALUES (90, 'Multiple pages of data from a text file');
INSERT INTO Articles VALUES (91, 'Build an Online 5 Star Rating System!');
INSERT INTO Articles VALUES (92, 'Uploading files in PHP');
INSERT INTO Articles VALUES (93, 'Working with forms in PHP');
INSERT INTO Articles VALUES (94, 'Installing Apache With SSL: The Complete Guide');
INSERT INTO Articles VALUES (95, 'SQL In Simple English Part 2/2');
INSERT INTO Articles VALUES (96, 'The Quickest Way To Count Users Online With PHP');
INSERT INTO Articles VALUES (97, 'Making Sense Of PHP Errors');
INSERT INTO Articles VALUES (98, 'PHP, MySQL and Authentication 101');
INSERT INTO Articles VALUES (99, 'PHP Date + Time Primer');
INSERT INTO Articles VALUES (100, 'Building A Document Request Protocol Part 2/2'); |
|
The Limit
The name of the game of paging with MySQL is the LIMIT part of the SELECT syntax. You can find the SELECT Syntax in the MySQL Manual at: http://www.weberdev.com/Manuals/MySQL5.1/sql-syntax.html#select.
LIMIT accepts two parameters. The 1st parameter tells MySQL from which record to start retrieving the results and the 2nd parameter tells MySQL how many results to retrieve. Some examples :
SELECT * FROM Articles LIMIT 0,5 will retrieve records 1-5
SELECT * FROM Articles LIMIT 5,5 will retrieve records 6-10
SELECT * FROM Articles LIMIT 5,10 will retrieve records 6-15
The Search
Let's create the next HTML page called Search.html:
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>Articles Search</TITLE>
</HEAD>
<BODY>
<CENTER>
<BR><BR>
<FORM NAME="ArticlesSearch" ACTION="Search.php" METHOD="POST">
<INPUT TYPE="TEXT" NAME="SearchString">
<INPUT TYPE="SUBMIT" NAME="SUBMIT" VALUE="Search">
</FORM>
</CENTER>
</BODY>
</HTML>
|
|
|
|
And a simple php page called Search.php : |
|
<?php
$SearchString=$_POST[SearchString];
mysql_connect("<host>","<user>","<pass>") or die("Unable to connect to SQL server");
mysql_select_db("<db name>") or die("Unable to SELECT DB");
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle") or die(mysql_error());
While($row = mysql_fetch_object($SearchResult)) {
Echo "<A HREF=\"ShowArticle.php?ArticleID=$row->ArticleID\">" . $row->ArticleTitle . "</A><BR>";
}
?>
|
|
|
Basically we now have the ability to write a search string in a textbox, submit the form and get a list of titles that have the string anywhere in the Title. But now, we want to show only 5 articles per page with an option to go to the next page to get the next 5 articles. We also want the option to go back and see the previous articles.
Paging
The paging is divided into two steps. In order to display the paging, you need to know how many pages there are in the search results. You get this number by getting the number of the results and divide it by the number of results per page. So basically you have:
|
|
<?php
$NumberOfResults=mysql_num_rows($SearchResult);
$Limit = 5; //Number of results per page
$NumberOfPages=ceil($NumberOfResults/$Limit);
?>
|
|
|
So we ran the query once and got the number of pages. Now we need to run the query again but this time, only bring the results for the relevant page. This will look like : |
|
<?php
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error());
?>
|
|
|
Notice that we now have the LIMIT in the query. $page is a parameter sent to the Search.php file from the navigation we are building. If we are on the 1st page, $page does not exist and we assume it's 1. So if $page is 1, we basically have LIMIT 0,5 which will retrieve records 1-5. If we click the navigation for the next page we will send $page=2. This will get LIMIT 5,5 which means records 5-10 and so on.
The Navigation bar
All that is left, is run in a loop for the number of pages we know we have and print the linkable number of the pages. Each link will call Search.php with the relevant page number and with this info we know which records to show.
|
|
<?php
$Nav="";
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<B>$i</B>";
}Else{
$Nav .= "<A HREF=\"Search.php?page=" . $i . "&SearchString=" .urlencode($SearchString) . "\">$i</A>";
}
}
Echo "<BR><BR>" . $Nav;
?>
|
|
|
This is the basic material you need for paging. To this example you can add features and styles to make it look and feel like your site.
Here is the complete code for Search.php : |
|
<?
//Connect to DB
mysql_connect("<host>","<user>","<pass>") or die("Unable to connect to SQL server");
mysql_select_db("<db name>") or die("Unable to SELECT DB");
$Limit = 5; //Number of results per page
$SearchString=$_POST["SearchString"]; // Get the search tearm from the FORM (POST)
If($SearchString == "") $SearchString=$_GET["SearchString"]; // Get the search tearm from the navigation (GET)
If($SearchString == "") {
Echo"Nothing to Search For";
exit();
}
$page=$_GET["page"]; //Get the page number to show
If($page == "") $page=1; //If no page number is set, the default page is 1
//Get the number of results
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult);
//Get the number of pages
$NumberOfPages=ceil($NumberOfResults/$Limit);
//Get only the relevant info for the current page using LIMIT
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error());
//Print the Titles
While($row = mysql_fetch_object($SearchResult)) {
Echo $row->ArticleTitle . "<BR>";
}
//Create and print the Navigation bar
$Nav="";
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<B>$i</B>";
}Else{
$Nav .= "<A HREF=\"Search.php?page=" . $i . "&SearchString=" .urlencode($SearchString) . "\">$i</A>";
}
}
Echo "<BR><BR>" . $Nav;
?>
|
|
|
Adding Next and Prev Links
If you want to complicate your navigation by adding << Prev and Next >> buttons you need to consider 3 options :
- You are on the 1st page, which means there is only Next>>
- You are on the last page, which means there is only <<Prev
- you are on any other page and you have them both.
Your new Navigation will look like this : |
|
<?php
$Nav="";
If($page > 1) {
$Nav .= "<A HREF=\"Search.php?page=" . ($page-1) . "&SearchString=" .urlencode($SearchString) . "\"><< Prev</A>";
}
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<B>$i</B>";
}Else{
$Nav .= "<A HREF=\"Search.php?page=" . $i . "&SearchString=" .urlencode($SearchString) . "\">$i</A>";
}
}
If($page < $NumberOfPages) {
$Nav .= "<A HREF=\"Search.php?page=" . ($page+1) . "&SearchString=" .urlencode($SearchString) . "\">Next >></A>";
}
?>
|
|
|
And the complete new code will look like this : |
|
<?
//Connect to DB
mysql_connect("<host>","<user>","<pass>") or die("Unable to connect to SQL server");
mysql_select_db("<db name>") or die("Unable to SELECT DB");
$Limit = 5; //Number of results per page
$SearchString=$_POST["SearchString"]; // Get the search tearm
If($SearchString == "") $SearchString=$_GET["SearchString"]; // Get the search tearm
If($SearchString == "") {
Echo"Nothing to Search For";
exit();
}
$page=$_GET["page"]; //Get the page number to show
If($page == "") $page=1; //If no page number is set, the default page is 1
//Get the number of results
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult);
//Get the number of pages
$NumberOfPages=ceil($NumberOfResults/$Limit);
$SearchResult=mysql_query("SELECT * FROM Articles WHERE ArticleTitle LIKE '%$SearchString%' ORDER BY ArticleTitle LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error());
While($row = mysql_fetch_object($SearchResult)) {
Echo $row->ArticleTitle . "<BR>";
}
$Nav="";
If($page > 1) {
$Nav .= "<A HREF=\"Search.php?page=" . ($page-1) . "&SearchString=" .urlencode($SearchString) . "\"><< Prev</A>";
}
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<B>$i</B>";
}Else{
$Nav .= "<A HREF=\"Search.php?page=" . $i . "&SearchString=" .urlencode($SearchString) . "\">$i</A>";
}
}
If($page < $NumberOfPages) {
$Nav .= "<A HREF=\"Search.php?page=" . ($page+1) . "&SearchString=" .urlencode($SearchString) . "\">Next >></A>";
}
Echo "<BR><BR>" . $Nav;
?>
|
|
|
|
|
| Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation | | | Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL | | | Alternating row colors with PHP and mySQL
Categories : PHP, Databases, MySQL, HTML and PHP | | | Date Arithmetic With MySQL
Categories : PHP, Databases, MySQL, Date Time | | | Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases | | | Building A Persistent Shopping Cart With PHP and MySQL
Categories : PHP, MySQL, Databases, Ecommerce | | | Start Using MySQL
Categories : MySQL, Databases, To MySQL, Beginner Guides | | | Referer Statistics
Categories : PHP, MySQL, HTTP, Databases | | | PHP 101 Part 8 of 15 : Databases and Other Animals
Categories : PHP, Beginner Guides, Databases | | | Custom MySQL-functions
Categories : Databases, MySQL, PHP, PHP Functions | | | PHP 101 Part 9 of 15 : SQLite My Fire!
Categories : PHP, Beginner Guides, Databases, SQLite | | | User identification using cookies in PHP and MySQL
Categories : PHP, Databases, MySQL, Cookies | | | Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML | | | PHP and MySQL News with Comments
Categories : PHP, Databases, MySQL | | | Practical Date and Time examples with PHP and MySQL
Categories : Databases, MySQL, PHP, Date/time | |
| |
|
|
