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)
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:
- Inserting Pages
- Inserting Keywords
- Relating Pages and Keywords to each other using 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

No comments:
Post a Comment