Showing posts with label php. Show all posts
Showing posts with label php. 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

Sunday, November 8, 2009

PHP: Extending mysqli for Simplified Multi Queries and Data Retrieval

Extending mysqli
Foreward (to my first php tutorial)
I decided to make this my first php/mysql tutorial, because in learning php/mysql I had a lot of problems with mysqli because I was using stored procedures and prepared statements. I believe most of my questions got answered in the user comments of the php documentation, but I personally think some of the points I will make in this blog cover features that are implemented in strange and specific ways by mysqli.

Explaination
In this tutorial I will be making a subclass of mysqli that takes care of some of the less obvious issues of mysqli functionality. The main points covered will be:

  • Connecting to a mysql database with php
  • Creating arrays from mysql output
  • Defining the Class, Constructor, and Destructor

class SQLConnection extends mysqli
{

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

public function __destruct()
{
$this->close();
}

}


Info about this code:

  • extends - Extends is the keyword for subclassing in php. If this doesn't mean anything to you, there is more info on it here.
  • Constructor Parameters - This constructor doesn't completely extend the mysqli constructor. If your project requires more functionality for connecting, you can expand the parameters of the constructor. I chose to keep a simple constructor because in most cases the most you need to log into a mysql database is a username, password, and database name. The first parameter of the parent constructor (currently set to null) tells mysqli to connect to the local host, which is what should be used if the mysql server is on the same computer as php.
  • Destructor - The destructor exists to destroy the connection when the object is destroyed. It is more of a good coding practice than neccessity, idealy, the connection will be closed by a call from outside this object, when the connection is no longer being used.

Getting Query Results


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

public function mq_result_array()
{
$this->next_result();
$result = $this->store_result();
return $this->result_array($result);
}


Info about this code:

  • result_array - mysqli returns results from queries in the form of mysqli_result objects. This function takes the data from a result and makes an array.
  • fetch_all(MYSQLI_ASSOC) - This function returns an array filled with associative arrays. The key strings correspond with the column names in the result table. Example: $data[5]['test_column'] Would return the value from row 5 of the column named 'test_column', in the results table stored by $data. Note: fetch_all does not exist before php version 5.3, I've included an alternative for older versions at the end of this tutorial.
  • $result->free() - This frees up system resources used by the result.
  • mq_result_array - mysqli returns results from multi_query's different than other queries. The results must be obtained by a call to next_result(). mq_result_array automatically grabs the next result and returns it as an array.

Closing Extra Results


public function close_results(){
while($this->more_results())
{
$this->next_result();
if($result = $this->use_result())
{
$result->free();
}
}
}


Info about this code:

  • close_results - An important part of mysqli is calling use_result or store_result on all results returned by any queries you make. If another query is made before using all results from the previous query, a session out of sync error occurs. To help avoid this error from occuring, close_results closes out any results left when it is called by scanning through the remaining results. This function is especially useful when calling stored procedures because they return an extra status result, which the mysqli documentation doesn't warn about.
  • while($this->more_results()) - This loop scans until $this->more_results() returns false, indicating that all results have been processed.
  • $this->next_result() - This must be called so that the next call to use_result() will return the next available result.
  • $result = $this->use_result() - This statement stores a reference to an unbuffered result. The result is unbuffered due to the call use_result() instead of the call store_result(). Unbuffered is ideal in this situation because the result is being freed up and not used so there is no reason to buffer it in php.
  • $result->free() - Frees resources associated with the used result.

Trying it out
Since this class is just intended to add some functionality, following are some simple examples that use the added functionality:

Create and Demo a Test Database (Requires MySQL permissions to create database/tables/procedures)
Note: Replace username and password with your own


require_once(dirname(__FILE__) . '/SQLConnection.php');
$connection = new SQLConnection('username','password','');
$connection->multi_query(''.
'CREATE SCHEMA IF NOT EXISTS test;'.
'USE test;'.
'DROP TABLE IF EXISTS TESTVALUES;'.
'CREATE TABLE TESTVALUES(intvalue INT, strvalue VARCHAR(255));'.
'INSERT INTO TESTVALUES(intvalue, strvalue)'.
'VALUES (0, \'zero\'), (1,\'one\'), (2, \'two\');');
$connection->close_results();
$connection->multi_query(''.
'SELECT * FROM TESTVALUES WHERE intvalue > 0;'.
'SELECT * FROM TESTVALUES WHERE CHAR_LENGTH(strvalue) = 4;');
$result = $connection->mq_result_array();
echo 'intvalue > 0: ';
print_r($result);
$result = $connection->mq_result_array();
echo 'charcount = 4: ';
print_r($result);
$connection->close_results();
$connection->close();
?>



Hopefully that code runs on your setup. Drop me a comment if there are any problems.

Troubleshooting from experience:

  • If you are using stored procedures, they must be called with multi_query, and they return an extra result that needs to be closed before you make another query. (When it comes to closing results I try to err on the safe side).
  • On the MySQL side of things, stored procedures with parameters don't use the query cache, so it can sometimes be better to hardcode your select statements in php.
  • Queries should not send unprocessed user input because of the possibility of SQL injection. Use mysql_real_escape_string() or prepared statements (not covered in this tutorial) to avoid this security pitfall.

Older version compatibility:
This class requires PHP 5, the following functions are for versions before 5.3 when mysqli::fetch_all() wasn't available.



public function result_array($result)
{
if(version_compare("5.3", phpversion(),"<=")) {
$data = $result->fetch_all(MYSQLI_ASSOC);
}
else
{
$data = $this->fetch_all($result);
}
$result->free();
return $data;
}

private function fetch_all($result)
{
$all_rows = array();
while ($all_rows[] = $result->fetch_assoc()){}
array_pop($all_rows);
return $all_rows;
}


The source code from this tutorial can be downloaded here.