How to do FULLTEXT search in CMS on CodeIgniter

On Habré lately, more and more posts devoted to the increasingly popular framework CodeIgniter appear. This is a fairly simple and convenient framework with which you can quickly start making normal PHP applications. Under the cat an example of the implementation of FULLTEXT search in CMS on CodeIgniter.

1. Preparation CodeIgniter.

Download and unpack the CodeIgniter distribution in any folder on your server. We create a database, a user with access rights to this database, and write connection settings in application / config / database.php.
Open application / config / config.php and set the base_url field setting.
')
2. Configure the Database.

We have already created a database in the first step, but we do not have tables. Create a simple table of pages.

CREATE TABLE pages (
id int (10) UNSIGNED NOT NULL AUTO_INCREMENT,
url text NOT NULL ,
title text NOT NULL ,
content text NOT NULL ,
updated datetime NOT NULL ,
PRIMARY KEY (id),
FULLTEXT KEY content (content)
) ENGINE=MyISAM

* This source code was highlighted with Source Code Highlighter .

When using FULLTEXT, you must specify all fields by which we will search. In this table, the search will be performed only in the content field. Please note that the FULLTEXT search only works using the MyISAM engine.

3. Test data

To test our search engine, you need to add a certain amount of test data to the table. I decided that Wikipedia can be a good contributor to our database. I wrote a small script that takes a few recent articles and then enters these articles into the database using Wikipedia export .
4. Search by database
For the search, we will use an SQL query of the form:

SELECT *
FROM pages
WHERE MATCH (content) AGAINST ( 'test' ) > 0

* This source code was highlighted with Source Code Highlighter .

When using the FULLTEXT search you need to remember a few limitations.

• The query length must exceed 3 characters, otherwise the search will return an empty result.
• There is a set of stop words that MySQL ignores when searching ('the', 'however', 'hello'). If you try to search for these words, the search result will be empty.
• Also available advanced search. Complete information can be found in the documentation for MySQL .

5. CodeIgniter and MVC pattern.

Let's return to our framework. CodeIgniter provides the popular model-view-controller design pattern.
Let me remind you the basic rules:

• All changes and work with the database made from the model
• Nothing can be displayed to the user from the controller or model.
• Only one expression can be inside the tags (shorthand PHP entry)

If you violate any of these rules, you should go back and think again about the structure of your project.
Let's start the search development with a model file. I use only one method that performs the search. You may notice that the “direct SQL query” is used here, although CodeIgniter has ActiveRecord allowing you to simplify the formation of queries.
Below is the model code located in the application / models / page_model.php

class Page_model extends Model
{
function Page_model()
{
parent::Model();
//
$ this ->load->database();
}
function search($terms)
{
//
$sql = "SELECT url, title
FROM pages
WHERE MATCH (content) AGAINST (?) > 0" ;
$query = $ this ->db->query($sql, array($terms, $terms));
return $query->result();
}
}

* This source code was highlighted with Source Code Highlighter .

The next step is to create a file of the form:

<? php $ this- > load- > helper('form'); ? >
<? php echo form_open ($ this- > uri- > uri_string); ? >
<? php echo form_label ( 'Search:' , 'search-box' ); ? >
<? php echo form_input ( array ( 'name' = > 'q', 'id' = > 'search-box', 'value' = > $search_terms)); ? >
<? php echo form_submit ( 'search' , 'Search' ); ? >
<? php echo form_close (); ? >
<? php if ( ! is_null ($ results )) : ? >
<? php if ( count ($ results )) : ? >
< ul >
<? php foreach ($ results as $ result ) : ? >
< li >< a href ="&# 60 ;? php echo $ result- > url; ? > " ><? php echo $ result- > title; ? ></ a ></ li >
<? php endforeach ? >
</ ul >
<? php else: ? >
< p >< em > There are no results for your query. </ em ></ p >
<? php endif ? >
<? php endif ? >

* This source code was highlighted with Source Code Highlighter .

Now it's time to create a controller file that will link the model and the view:

class Pages extends Controller {
function search($search_terms = '' )
{
// URL
// ,
// .
if ($ this ->input->post( 'q' ))
{
redirect( '/pages/search/' . $ this ->input->post( 'q' ));
}
if ($search_terms)
{
//
$ this ->load->model( 'page_model' );
$results = $ this ->page_model->search($search_terms);
}
//
$ this ->load->view( 'search_results' , array(
'search_terms' => $search_terms,
'results' => @$results
));
}
}

* This source code was highlighted with Source Code Highlighter .

At the beginning of this method, we used redirect functions. This function is contained in the URL helper. To use this function, we made a "manual" helper loading using $ this-> load-> helper ('url'); Because we will often use this helper, it is easier to immediately register it in autoload, so as not to load it all the time manually. To do this, edit the file application / config / autoload.php. We add to the $ autoload ['helper'] section:

$autoload[ 'helper' ] = array( 'url' );

* This source code was highlighted with Source Code Highlighter .

Now we have a working prototype. Do not forget that your database must have data (at least 3 rows). Now type in your browser something like 'http: //localhost/index.php/pages/search', enter in the search bar any text contained in the database and click search. The result will look something like this:


6. Adding new features to our search.

To begin, add pagination for search results. To display pagination in CodeIgniter, the Pagination class is used.
Modify our model to display paginated navigation. To do this, we need to specify the number of records selected from the database, the record number from which to select, as well as get the total number of records for this query.
Here's what we got:

class Page_model extends Model {
function search($terms, $start = 0, $results_per_page = 0)
{
//
//
if ($results_per_page > 0)
{
$limit = "LIMIT $start, $results_per_page" ;
}
else
{
$limit = '' ;
}
// SQL
$sql = "SELECT url, title, content
FROM pages
WHERE MATCH (content) AGAINST (?) > 0
$limit" ;
$query = $ this ->db->query($sql, array($terms, $terms));
return $query->result();
}
function count_search_results($terms)
{
// Run SQL to count the total number of search results
$sql = "SELECT COUNT(*) AS count
FROM pages
WHERE MATCH (content) AGAINST (?)" ;
$query = $ this ->db->query($sql, array($terms));
return $query->row()->count;
}
}

* This source code was highlighted with Source Code Highlighter .

The next step is to modify our controller to work with page navigation.

class Pages extends Controller {
function search($search_terms = '' , $start = 0)
{
// URL
// ,
// .
if ($ this ->input->post( 'q' ))
{
redirect( '/pages/search/' . $ this ->input->post( 'q' ));
}
if ($search_terms)
{
//
//
$results_per_page = $ this ->config->item( 'results_per_page' );
// , ,
//
$ this ->load->model( 'page_model' );
$results = $ this ->page_model->search($search_terms, $start, $results_per_page);
$total_results = $ this ->page_model->count_search_results($search_terms);
//
$ this ->_setup_pagination( '/pages/search/' . $search_terms . '/' , $total_results, $results_per_page);
//
$first_result = $start + 1;
$last_result = min($start + $results_per_page, $total_results);
}
//
$ this ->load->view( 'search_results' , array(
'search_terms' => $search_terms,
'first_result' => @$first_result,
'last_result' => @$last_result,
'total_results' => @$total_results,
'results' => @$results
));
}
function _setup_pagination($url, $total_results, $results_per_page)
{
//
$ this ->load->library( 'pagination' );
$uri_segment = count(explode( '/' , $url));
//
//
$ this ->pagination->initialize(array(
'base_url' => site_url($url),
'uri_segment' => $uri_segment,
'total_rows' => $total_results,
'per_page' => $results_per_page
));
}
}

* This source code was highlighted with Source Code Highlighter .

Let's take a look at what we did. We added pagination, which means we added new segments to the URL. This segment is both a search results page and a number to determine the starting position for a sample of the database.
To set the number of results on the search page, I used the config library . It is best to add a new file to the application / config folder. Create a file application / config / application.php containing the line:

$config[ 'results_per_page' ] = 10;

* This source code was highlighted with Source Code Highlighter .

Add it to autoload by changing the application / config / autoload.php file again and add the 'application' to the $ autoload ['config'] section.
We also use methods to process the information displayed in the search results (snippet and subtitle in the snippet of words from the query). These methods are contained in our helper in the file application / helpers / search_helper.php. The first function search_highlight ($ text, $ search_terms) selects words from the query in the search results, the second search_extract ($ content, $ search_terms, $ number_of_snippets = 3, $ snippet_length = 60) extracts from the content the part containing the search specified in the search.
This is how our modified look file will look like:

<? php $ this- > load- > helper(array('form', 'search')); ? >
<? php echo form_open ($ this- > uri- > uri_string); ? >
<? php echo form_label ( 'Search:' , 'search-box' ); ? >
<? php echo form_input ( array ( 'name' = > 'q', 'id' = > 'search-box', 'value' = > $search_terms)); ? >
<? php echo form_submit ( 'search' , 'Search' ); ? >
<? php echo form_close (); ? >
<? php if ( ! is_null ($ results )) : ? >
<? php if ( count ($ results )) : ? >
< p > Showing search results for ' <? php echo $ search_terms ; ? > ' ( <? php echo $ first_result ; ? > – <? php echo $ last_result ; ? > of <? php echo $ total_results ; ? > ): </ p >
< ul >
<? php foreach ($ results as $ result ) : ? >
< li >< a href ="&# 60 ;? php echo $ result- > url; ? > " ><? php echo search_highlight ($ result- > title, $search_terms); ? ></ a >< br /><? php echo search_extract ($ result- > content, $search_terms); ? ></ li >
<? php endforeach ? >
</ ul >
<? php echo $ this- > pagination- > create_links(); ? >
<? php else: ? >
< p >< em > There are no results for your query. </ em ></ p >
<? php endif ? >
<? php endif ? >

* This source code was highlighted with Source Code Highlighter .

And so new search results:


7. Even more tasty chips.

CodeIgniter provides the ability to display test data and various additional information .
For example, you can run the test for the model:

// Mark the start of search
$ this ->benchmark->mark( 'search_start' );
// Load the model, perform the search and establish the total
// number of results
$ this ->load->model( 'page_model' );
$results = $ this ->page_model->search($search_terms, $start, $results_per_page);
$total_results = $ this ->page_model->count_search_results($search_terms);
// Mark the end of search
$ this ->benchmark->mark( 'search_end' );

* This source code was highlighted with Source Code Highlighter .

As a result, the form with the test results will look like this: