Showing posts with label engine. Show all posts
Showing posts with label engine. Show all posts

Monday, November 16, 2009

MySQL / PHP Search Engine Tutorial (5/5): Creating the User Interface

Creating a Basic Search Interface

Intro:
This tutorial will demonstrate two parts of creating the user interface:
A HTML form code which can be placed in a page to allow searching of the website.
  1. Code to create results.php, the page which will display the results of a search.
  2. The results will be displayed as a simple list of links and descriptions.
Creating the HTML Search Form:
This simple HTML interface for search is an HTML form with a text input, and a submit button. This code belongs in any page from which you want a user to be able to search:

<form method="get" action="results.php">
<input maxlength="60" name="s">
<input class="button" value="Search" type="submit">
</form>
Code Info:
Path:
If searches are performed from pages in multiple directories, and/or results.php is not in the same directory as the pages performing the search, "results.php" should be replaced with a full path such as "http://mysitename.com/results.php".

Creating the Results Page (results.php):
The results page is going to be a simple html page that spits out the search results, in order of relevance, as a line break seperated list of links and descriptions. It also contains a search form at the top of the page.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Search Results</title>
</head>
<body>
<!-- The search form -->
<form method="post" action="results.php">
<input maxlength="60" name="s">
<input class="button" value="Search" type="submit">
</form><br/>

<!-- Results that will display if a search string has been posted -->
<?php
// Check if a search string has been posted
if(isset($_POST['s']))
{
require_once('SearchSQL.php');

// Create a SearchSQL object to perform the search
$connection = new SearchSQL('user', 'password', 'page_search');
// Perform the search, store the results and free the resources
$results = $connection->search($_POST['s'], $error);
$connection->close();

if($results)
{
// If $results isn't false, print out the results
echo 'Results found:<br/><br/>';
foreach($results as $result)
{
echo '<a href="'.$result['page_url'].'">'.$result['page_title'].'</a><br/>';
echo $result['page_desc'];
echo '<br/><br/>';
}
}
else
{
// Results are false, print out the reason why
echo $error;
}
}
?>
</body>
</html>

Code Info:
Formatting:
This code couldn't be formatted like the rest of the code in these tutorials because the <br/>'s get turned into actual line breaks in the code.
Changes: user and password parameters for new SearchSQL() need to be set to your own MySQL username and password.

Fin:
So hopefully you now have a page keyword search up and running, if you have a problems/questions/comments/feature requests drop me a comment in any of the tutorials of this series.

The source code for this tutorial can be found here.
The source code for all 5 tutorials in this series can be found here.

Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface

MySQL / PHP Search Engine Tutorial (4/5): Search Query Class

Creating the Search Query Class

Requirements:
  • PHP - version 5.3.0 used. Should be set up on same machine as the MySQL server, unless you modify the code in ParseSQL.
  • page_search - The database defined in tutorial one of this series should be created and accessible on your MySQL server. In order for searches to return any results, the database should also contain some data.

Intro:
In this entry of the series we will be creating a class for performing a search query on the MySQL database defined in part one. The class SearchSQL is an extensino of the mysqli class in PHP, and will simplify performing the search query, and retrieving the results in array form.

Defining the SearchSQL Class:


class SearchSQL extends mysqli
{
public function __construct($username, $password, $database)
{
parent::__construct(null, $username, $password, $database);
}
}

Code Info:
This code creates the class ParseSQL as a subclass of mysqli. The mysqli (MySQL Improved) class in PHP is used for accessing MySQL databases in PHP. ParseSQL overrides the constructor of mysqli to decrease the number of parameters necessary. By setting the first parameter to null on the mysqli constructor, PHP attempts to connect to a MySQL server at host address 'localhost'. If the MySQL server you are using isn't on the same machine as your PHP you will have to change the 'null' to the address where you MySQL server resides. Username, password, and database are all login credentials. Username and password are self explanatory, and database is the name of the database that will be used when sending queries with this mysqli object. Now that we have basic MySQL access in this class, it's time to analyze the requirements for the queries this class will be sending.

Analyzing Requirements:
This search has fairly simple requirements. It needs to accept a space delimited search string. It will split the string into words, and search for occurences similar to each word in the database table KEYWORD. It will then return all of the pages which contain text matching the search, ordered according to how many matches each page had. These requirements will be achieved using 3 functions:
  • search() - This function will perform a search query, and return the results.
  • term_string() - This function will be used to change the provided search string into a format that can be used in a MySQL query.
  • result_array() - This function will be used to convert the mysqli result object into an array so it can be used by other php code.

Creating the search() Function:
This function will be responsible for performing actual searches. It will return info from the search in the form of an associative array that represents the table returned by MySQL. The code is as follows:


public function search($search, &amp;$error)
{
// Get the compound or statement for term comparison
$or_string = $this->term_string($search);

// If there are no terms in $search, return error
if($or_string == false)
{
$error = 'No valid search terms in: ' . $search;
return false;
}

// Send search query
$result = $this->query('SELECT page_url, page_title, page_desc, COUNT(page_url)
FROM (SELECT key_id FROM KEYWORD WHERE '.$or_string.') AS SEARCHTERMS
NATURAL JOIN TERM
NATURAL JOIN PAGE
GROUP BY page_url
ORDER BY COUNT(page_url) DESC');

// If result contains no rows return no matched found error
if($result->num_rows == 0)
{
$error = 'No matches found for: ' . $search;
return false;
}

// Return array of results
return $this->result_array($result);
}

Code Info:
Parameters:
$search = A string of space delimited search terms. $error = A string passed by reference. If this function returns false, it will return a reason for returning false so that the user can be provided with logically labeled errors.
Line 14: This SELECT statement will return the page_url, page_title, page_description, and number of word matches found per page.
Line 15: This part of the FROM clause will get all the KEYWORD entries that are considered matched by the statement returned from term_string().
Line 16: This will JOIN the search terms with the id's of the pages that contain them.
Line 17: This will JOIN the page data with each result of the search.
Line 18: This groups the pages so that each url will only be reported once.
Line 19: This orders the results by the number of times each url is matched. DESC means 'descending'. This means that the page with the most matches will occur at array index 0, and matches will be descending from there.

Creating the term_string() Function:
This function splits apart the provided search string, and creates a series of MySQL LIKE statements that are joined by OR statements. This format will allow each search term to be compared to each word in table KEYWORD.


private function term_string($search)
{
// Clean input
$search = strtolower(preg_replace("[^A-Za-z0-9]", " ", $search));

// Split into unique words
$words = array_unique(explode(" ", $search));

$result = "";
// Count the number of words that have been added to the statement
$term_count = 0;
// Scan through $words
for ($i = 0; $i <> 0)
{
// If this is the first time use a '(' at the beginning of the statement
// instead of an OR
if($term_count > 0)
{
$result .= 'OR KEYWORD.keyword LIKE "%'.$words[$i].'%" ';
}
else
{
$result .= '(KEYWORD.keyword LIKE "%'.$words[$i].'%" ';
}
$term_count += 1;
}
}

// If atleast one term has been added, return the result string
// with a ')' added to close the parenthesis, otherwise return false.
return ($term_count > 0) ? $result . ')' : false;
}

Code Info:
Functionality Note: This keyword parser is very basic. If a search term contains punctuation, it will be removed and replaced with spaces. (example: The word 'don't' would become entries 'don' and 't') Also, there is no length checking on the search terms. Due to the wildcard ('%') characters used, search terms that are one character long will return a match for every word containing that letter. If you are interested in seeing a tutorial with some ideas on how to fix these issues, drop me a line in the comments of this entry.
LIKE in MySQL: The LIKE operator is a way of comparing string in MySQL. The documentation for LIKE statements can be found here.

Creating the result_array() Function:
The results array is a simple function that combines some mysqli functionality to return the table contained in a mysqli_result object as an array, and then free the system resources associated with that result object.


private function result_array($result)
{
$data = $result->fetch_all(MYSQLI_ASSOC);
$result->free();
return $data;
}

Code Info:
Associative Array Format: fetch_all(MYSQLI_ASSOC) returns the table contained in the result object in an associative format. If printed out, that means the array would look something like this:
array( 0 => array(col1 => val1, col2 => val2, ...), 1 => array(col1 => val1, col2 => val2, ...), ...)
This means retrieving data from the column of a specific row is formatted as follows:
$arrayname[row number][column name]

Using SearchSQL:
This function creates some ugly output, but will indicate if SearchSQL is working correctly or not.

<?php
require_once(dirname(__FILE__) . '/SearchSQL.php');
$connection = new SearchSQL('user','password','page_search');
$result = $connection->search("some search terms", $error);
if($result == false)
{
echo $error;
}
else
{
print_r($result);
}
$connection->close();
?>


The source code for this tutorial can be found here.
The source code for all 5 tutorials in this series can be found here.

Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface

MySQL / PHP Search Engine Tutorial (3/5): Parsing Webpages

Parsing Webpage Content Into Keywords

Requirements:
  • PHP - version 5.3.0 used. Should be set up on same machine as the MySQL server, unless you modify the code in ParseSQL.
  • simple_html_dom - version 1.11 used. This class enables PHP to extract information from HTML pages.
  • ParseSQL - Class made in tutorial two of this series. Used for adding parsed data to the MySQL database.
  • page_search - The database defined in tutorial one of this series should be created and accessible on your MySQL server.
Analyzing Requirements:
At the end of the "Filling the Database" tutorial, we had created a database for inserting pages and search keywords, and a class for performing the insertions. The next step is to retrieve data for insertion. For this purpose, we will create a static class named HTMLParser. HTMLParser will need to be able to perform the following tasks:
  1. Use an instance of ParseSQL to perform data insertion.
  2. Open a HTML file on the local server, and optionally on a remote server. (if PHP settings support it)
  3. Extract the title, description, and any textual content from the HTML file.
  4. Convert local file paths to URLs so they can be used to create links.
  5. Scan a file system at a certain url or path in order to list and parse files (This way each url which should support searching won't have to be individually entered by hand)
  6. Check the file extension each file in a directory to determine if it potentially contains HTML.
All of these requirements will be met using the four static functions of HTMLParser:
  • parsePage - Requirements: 1, 2, 3
  • fileToURL - Requirement: 4
  • parseDirectory - Requirement: 5
  • hasValidExt - Requirement: 6

Creating the HTMLParser Class:


require_once(dirname(__FILE__) . '/simple_html_dom.php');
require_once(dirname(__FILE__) . '/ParseSQL.php');

abstract class HTMLParser
{

}

Code Info:
This is just a simple abstract class definition with two required classes. The required pathnames are set up for ParseSQL and simple_html_dom to be placed in the same directory as HTMLParser, you can adjust them to your need. Because the class is abstract, it cannot be instantiated (new HTMLParser() is invalid), so there is no need to define a constructor.

Defining the parsePage() Function:
This function will take an initialized ParseSQL instance, a path string that can either be a url or a local file path, and a boolean 'islocal' which is should be true if the path string is a file path. It will use these parameters to parse the file specified by the path string, and add it to the search database used by the ParseSQL instance. The code is as follows:


public static function parsePage($parse_sql, $path_name, $islocal)
{
// Create an html dom from the provided pathname
$html = file_get_html($path_name);

// Verify the file contains an html tag
if($html->find('html',0))
{
// if pathname is a local file, convert to url
$url = ($islocal ? HTMLParser::fileToURL($path_name) : $path_name);
// Get the title of this page, if no title tag is set, use the url
$title = $html->find('title',0);
$title = ($title == null ? $url : $title->innertext);
// Get the description meta tag of this page, or null if there isn't one
$desc = $html->find('description',0);
$desc = ($desc == null ? $desc : $desc->innertext);
// Extract an array of keywords from this page.
$terms = ParseSQL::extractKeywords($html->plaintext);
// Add Page to database and store the page id
$page_id = $parse_sql->addPage($url, $title, $desc);
// Add terms to database using the page id
$parse_sql->addTerms($terms, $page_id);
}
}

Code Info:
Using a URL for $path_name: If a url is specified in $path_name, PHP needs to have fopen() set up to be able to open remote files. Info about setting up php to allow_url_fopen can be found here.

Defining the fileToURL() Function:
I slightly modified a function submitted by someone in the PHP doc comments to create this function. It takes a file path, and uses the PHP home directory and server url to determine the url that represents that file path. It will only work on file paths that are sub-paths of the home directory. The code is as follows:


private static function fileToURL($file_path) {
// Get the resolved path of the provided file path
$full_path = realpath($file_path);
// Get just the directory part of the provided file path
$dir_path = realpath(dirname($file_path));
// Get the root path of the web server
$root_path = realpath($_SERVER['DOCUMENT_ROOT']);

if (strlen($root_path) > strlen($dir_path)) {
// File is lower than root
return false;
}

// Create a url from the provided path
return 'http://' . $_SERVER['HTTP_HOST'] . str_replace('\\', '/', substr($full_path, strlen($root_path)));
}

Code Info:
Line 15: This line essentially replaces the home path of the server with the url of the server. substr() eliminates the root file path from the provided file path. str_replace() replaces '\' characters with '/' characters, because windows file paths use '\' slashes for file paths. Altogether this function will create a valid url that can be used to link to the file provided.

Creating the parseDirectory() Function:
This function is used to scan the files in a directory, or at an url that allows indexing. Same as the parsePage() function, PHP needs to have fopen() set up to be able to open remote files. Info about setting up php to allow_url_fopen can be found here. The code is as follows:


public static function parseDirectory($parse_sql, $path, $deep_scan = false, $valid_types = array('html', 'htm', 'php'))
{
// Create a DirectoryIterator for single directory search,
// or a RecursiveDirectoryIterator to also scan subdirectories
$dir = ($deep_scan == true ? new RecursiveDirectoryIterator($path) : new DirectoryIterator($path));

// Scan through each file
foreach($dir as $page)
{
if($page->isDot() !HTMLParser::hasValidExt($page->getFilename(), $valid_types))
{
// If page is a '.' or '..' directory, or has an extension not in $valid_types
// continue to next file
continue;
}
else
{
// If page has a valid extension attempt to parse it
HTMLParser::parsePage($parse_sql, $page->getPathName(), true);
}
}
}

Code Info:
Parameters: $parse_sql = an initialized instance of ParseSQL, $path - a string representing the directory containing the files to be parsed, $deep_scan - If set to true, subdirectories of the provided path will also be parsed, $valid_types - An array containing the lower case extensions of filetypes that should be scanned, defaults to php, htm, and html files.
Line 8: DirectoryIterator can be used in a foreach loop to scan through each value of the DirectoryIterator object. More info on DirectoryIterator can be found at the php documentation here.
Line 10: hasValidExtension() is defined next, it will verify that the file currently being scanned has an extension included that is contained in $valid_types.

Creating the hasValidExtension() Function:
This function returns a boolean value indicating if the extension of a provided file string is contained in a provided array. The code is as follows:


private static function hasValidExt($filename, $valid_types)
{
return in_array(strtolower(pathinfo($filename, PATHINFO_EXTENSION)), $valid_types);
}

Code Info:
Code Broken Down: pathinfo($filename, PATHINFO_EXTENSION) gets the file extension part of a filename as a string. strtolower() converts the extension to lowercase so that case won't affect the comparison with array values. in_array() determines if the file extension is present in $valid_types.

Example Usage of HTMLParser:
This code will parse the pages in the path it is contained in:


<?php
require_once(dirname(__FILE__) . '/HTMLParser.php');
require_once(dirname(__FILE__) . '/ParseSQL.php');
$connection = new ParseSQL('user','password','page_search');
HTMLParser::parseDirectory($connection, dirname(__FILE__) . '/');
$connection->close();
?>


The source code for this tutorial can be found here.
The source code for all 5 tutorials in this series can be found here.

Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface

Thursday, November 12, 2009

MySQL / PHP Search Engine Tutorial (2/5): Filling the Database

Filling a MySQL Search Keyword Database using PHP

Requirements:

  • PHP - version 5.3.0 used. Should be set up on same machine as the MySQL server. I will note where code must be changed if the MySQL server is on a remote machine.
  • page_search - The database defined in tutorial one of this series should be created and accessible on your MySQL server.

Intro:
In this entry of the series we will be creating a class for easily inserting data into the database defined in part one. The class ParseSQL is an extension of the mysqli class in PHP, and will simplify the use of multiple queries to insert pages and search terms into the database page_search.

Defining the Class:
This class should be created in the file 'ParseSql.php'.


class ParseSQL extends mysqli
{
public function __construct($username, $password, $database)
{
parent::__construct(null, $username, $password, $database);
}
}

Code Info:
This code creates the class ParseSQL as a subclass of mysqli. The mysqli (MySQL Improved) class in PHP is used for accessing MySQL databases in PHP. ParseSQL overrides the constructor of mysqli to decrease the number of parameters necessary. By setting the first parameter to null on the mysqli constructor, PHP attempts to connect to a MySQL server at host address 'localhost'. If the MySQL server you are using isn't on the same machine as your PHP you will have to change the 'null' to the address where you MySQL server resides. Username, password, and database are all login credentials. Username and password are self explanatory, and database is the name of the database that will be used when sending queries with this mysqli object. Now that we have basic MySQL access in this class, it's time to analyze the requirements for the queries this class will be sending.

Analyzing Requirements:
At the end of the first tutorial we had defined our database to look like:

PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, key_id)
KEYWORD(key_id, keyword)

The two main entities (data items) of this database are pages and keywords. Pages and keywords are related using the table TERM. TERM contains an entry for each unique word occuring on each page. Using this database, the three main functions we need to perform when inserting data are:
  1. Inserting Pages
  2. Inserting Keywords
  3. Relating Pages and Keywords to each other using TERM.
These three requirements can be achieved using two methods, addPage(), and addTerms(). addPage() will be used to add pages to the PAGE table. addTerms() will be used to add words to the KEYWORD table if they don't already exist, and then relate them to a page using TERM. It's easiest to combine the last 2 requirements into one function, because there is no reason to add keywords to the KEYWORD table if they are not being used as a TERM.

Creating the addPage() Function:
This function will be responsible for adding/updating pages. It will either either add the values passed, or if the $url already exists in the database, the title and description will be updated. This function will also be responsible for removing old TERM entries related to the page that is being added. The code is as follows:

public function addPage($url, $title, $desc)
{
// Insert the page info
$stmt = $this->prepare('INSERT INTO PAGE(page_url, page_title, page_desc) VALUES (?,?,?) ON DUPLICATE KEY UPDATE page_title = ?, page_desc = ?');
$stmt->bind_param("sssss", $url, $title, $desc, $title, $desc);
$stmt->execute();
$stmt->close();

// Get the page id
$stmt = $this->prepare('SELECT page_id FROM PAGE WHERE page_url = ?');
$stmt->bind_param("s", $url);
$stmt->execute();
$stmt->bind_result($page_id);
$stmt->fetch();
$stmt->close();

// Delete pre-existing page keywords
$stmt = $this->prepare('DELETE FROM TERM WHERE page_id = ?');
$stmt->bind_param("i", $page_id);
$stmt->execute();
$stmt->close();

return $page_id;
}

Code Info:
Prepared Statements: addPage() uses prepared statements to send queries to the database. This is so that strings can be inserted safely without escaping them. For more info on prepared statements and why/when to use them, read the documentation here.
Line 1: The params $url, $title, $desc are strings specifiying the url, title, and description, of the page being added.
Line 4: This statement prepares a simple INSERT query that will add the 3 provided parameters to the PAGE table in MySQL.
Line 10: This SELECT statement retrieves the 'page_id' of the page that was just added. This information is needed so that pre-existing terms related to this page can be deleted, and new terms can be added with the correct value for column 'page_id' in TERM.
Line 18: This DELETE statement removes all pre-existing TERM entries related to this page. These entries should be removed so that old search terms will not remain when page content is updated.
Line 23: This return statement returns the page_id of the added page, because it will be needed in the next call to addTerms() in order to specify the page which the terms occur on.

Creating the addTerms() function:
The addTerms function is responsible for relating words to a PAGE. It uses parameters $keywords (an array of keyword strings) and $page_id (the page_id of the url containing the keywords). addTerms() will add any non-existing words to the KEYWORD table, and create TERM entries for each keyword being related to a page. The code is as follows:

public function addTerms($keywords, $page_id)
{
// Turn of autocommit, it is faster to commit many inserts at once
$this->autocommit(false);

// Insert any keywords that don't already exist in table KEYWORD
$stmt = $this->prepare('INSERT IGNORE INTO KEYWORD(keyword) VALUES (?)');
foreach($keywords as $keyword)
{
// Skip 0 length strings
if(strlen($keyword) > 0)
{
$stmt->bind_param("s", $keyword);
$stmt->execute();
}
}
$stmt->close();
// Commit the added keywords
$this->commit();

// Relate the keywords to the $page_id provided
$stmt = $this->prepare('INSERT INTO TERM(key_id, page_id) SELECT KEYWORD.key_id, ? FROM KEYWORD WHERE keyword = ?');
foreach($keywords as $keyword)
{
if(strlen($keyword) > 0)
{
$stmt->bind_param("is", $page_id, $keyword);
$stmt->execute();
}
}
$stmt->close();
// Commit all tags and turn autocommit back on
$this->commit();
$this->autocommit(true);
}

Code Info:
Reusable Prepared Statement:
This function reuses a prepared statements multiple times to insert words and TERM entries. This is an advantage of prepared statements, after they have been prepared, they can be bound with new parameters and executed multiple times before they are closed. This allows for only the parameters to be sent to MySQL in the multiple calls, making the communication between MySQL and PHP shorter.
Autocommit: Commit makes the changes from a MySQL session available to all users of the database. Normally MySQL starts up with autocommit turned on, meaning that each time you execute a statement, they changes are immediately applied. By turning off autocommit at the beginning of a large number of insert statements, and only calling commit when necessary, the operations can be made to run much faster. This function takes advantage of manually commiting necessary changes.
First 'for' Loop: This loop is used to execute the statement for inserting keywords on each member of the $keywords array. At the end of the loop, the inserts are committed so that they recieve key_id's and the next loop can relate keywords to pages.
Last 'for' Loop: This loop inserts TERM entries which relate keywords to pages. It uses the $page_id function parameter to define the page_id entry in TERM. It uses a SELECT clause in the INSERT query to find the 'key_id' of each word in the $keywords function parameter for the key_id column of terms.
Summary: At the end of this function, all entries in $keywords will exist in the table KEYWORD, and be related to the PAGE entry having the value 'page_id' passed in the function parameters.

One Last Function:
Because most classes that will be using the ParseSQL class will need to create an array of keywords from a string, it makes sense to include a public static function that will allow them to easily parse a space delimited string into an array of keywords. The code is as follows:

public static function extractKeywords($data)
{
// Get content with html and php removed
$content = strip_tags($data);
// Convert to alphanumeric lower case string
$content = strtolower(preg_replace("/[^A-Za-z0-9]/", " ", $content));
// Split into unique words
return array_unique(explode(" ", $content));
}

Code Info:
Limitations: This is just a simple way to get a list of keywords from a space delimited string. This method replaces all non-alphanumeric characters with spaces, so it will not be able to correctly parse hyphenated and contracted words, or punctuated words such as urls. This function is just a simple way to parse keywords, and it has been kept seperate as a static function so that other classes that use ParseSQL can find keywords in a more complex way if desired.

Final Notes:
In this tutorial I won't be including examples using the 'ParseSQL' class, because the class 'HTMLParser', defined in the tutorial, will be an example of how to use ParseSQL.

The source code for this tutorial can be found here.
The source code for all 5 tutorials in this series can be found here.

Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface

MySQL / PHP Search Engine Tutorial (1/5): Creating a Database

Defining a MySQL Database to be Populated / Searched using PHP

Requirements:

Analyzing Requirements:
The first step in creating the database to store information for searching, is deciding on the desired features of the search engine. This is also a good time to figure out exactly what the search query will be, so that the database can be constructed in a way that makes it efficient.

The feature list decided upon for this tutorial is as follows:

  • The search engine should take as input a string of space delimited keywords.
  • The keywords will each be compared to page contents individually (no capability of searching for phrases)
  • Keywords being searched for will be considered matched if the word is contained on the page in any form. (Similar to the common text editor "find" function, with "match whole word only" unchecked)
  • Results will be sorted according to the number of word matches, with duplicate words on the page, or in the search string ignored. (The search string "search this" would return the same order as "search this this")
  • Results of a search will be a table, ordered as described above, with columns containing the urls, titles, and descriptions of pages on which matches were found.
Design Decisions:
With the feature list created, it's now possible to determine the requirements of the database. Following is an intuitive breakdown of deciding how to create the data structure of this database:

  • The 4 pieces of data needed to perform a search are: Page URL, Page Title, Page Description, and Words Contained on the Page.
  • Page URL is unique to each page, and can be used to determine the other 3 properties, so 'page_url' will be the Primary Key of the table PAGE. Page Title and Page Description are values that only occur once per page, and are usually unique on each page. They can simply be added to table PAGE as columns 'page_title' and 'page_desc'.
Database Looks Like:
PAGE(page_url, page_title,page_desc)
  • "Words contained on the Page" is more complicated, because it has multiple words per page, and each word be contained on more than one page. A new table called TERM is created with columns page_url and keyword. Also, now that page_url is contained in both tables, an id column will be added to PAGE, and page_url in table TERM will be replaced with an id.
Database Looks Like:
PAGE
(page_id, page_url, page_title, page_desc)
TERM(page_id, keyword)
  • Now the table TERM will have a large amount of duplicate data in each column, because many words occur on more than one page, and many pages contain more than one word. To fix this, a table called KEYWORD is created, and the column 'keyword' in table TERM is replaced with an id that points to an entry in KEYWORD.
Database Looks Like:
PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, key_id)
KEYWORD(key_id, keyword)

Pros and Cons of Database:
The database is now the ideal structure to meet our search requirements. There are some definite advantages of splitting up (normalizing) the database this way:
  • When a search is performed, search terms can be compared to the words in KEYWORD, if 'keyword' had been left as a column in TERM then multiple duplicate comparisons would have been made.
  • Using number id's for words and pages will cut down the size of the database by eliminating VARCHAR's stored in multiple places.
The database also has some limitations to keep in mind:

  • Using number id's limits the max number of unique pages and words that can be stored to the max size of the number used. On a personal scale site this shouldn't be an issue, so it is an acceptable limitation.
  • The id's in this database are foreign keys (they are used to point to data in another table). In MySQL, InnoDB is the only database engine that supports foreign key constraints. More on this later.
Creating the Database
The following commands can be entered into a MySQL command client to create the page_search database. Alternatively, the file 'page_search.sql' can be found here. It can be loaded and used from the MySQL command client using the command '\. [filename]' (without quotes).
CREATE SCHEMA IF NOT EXISTS `page_search` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `page_search`;

CREATE TABLE IF NOT EXISTS `page_search`.`PAGE` (
`page_id` INT NOT NULL AUTO_INCREMENT ,
`page_url` VARCHAR(255) NOT NULL ,
`page_title` VARCHAR(255) NULL ,
`page_desc` VARCHAR(255) NULL ,
UNIQUE INDEX `UNIQUE` (`page_url` ASC) ,
PRIMARY KEY (`page_id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `page_search`.`KEYWORD` (
`key_id` INT NOT NULL AUTO_INCREMENT ,
`keyword` VARCHAR(26) NOT NULL ,
PRIMARY KEY (`key_id`) ,
UNIQUE INDEX `UNIQUE` (`keyword` ASC) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `page_search`.`TERM` (
`page_id` INT NOT NULL ,
`key_id` INT NOT NULL ,
PRIMARY KEY (`page_id`, `key_id`) ,
INDEX `KEY_F` (`key_id` ASC) ,
INDEX `KEY_P` (`page_id` ASC) ,
CONSTRAINT `KEY_F`
FOREIGN KEY (`key_id` )
REFERENCES `page_search`.`KEYWORD` (`key_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `KEY_P`
FOREIGN KEY (`page_id` )
REFERENCES `page_search`.`PAGE` (`page_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
This tutorial won't go into depth on what this MySQL code means because it is just standard SQL and it follows the plan for the requirements laid out in this tutorial. One thing to mention, if InnoDB is not enabled for your MySQL Server, the FOREIGN KEY statements will be ignored. Because this series of tutorials doesn't do any deleting of values or updating of foreign keys it should behave the same way out of InnoDB. However if you expand the code, keep in mind that MySQL will not use the foreign key constraints unless the table engine is InnoDB.

Finally, you can download the sql and MySQL Workbench files for this tutorial here.
The source code for all 5 tutorials in this series can be found here.

Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface