How to use fulltext index search in MariaDB and PHP

Home > Search > How-to
  by

Create the full text index

When creating a new table, assign one or more columns to the fulltext index.  In this example, the column named Column1 is assigned to the fulltext index.

create table MyTable (
  Column1 text,
  fulltext (Column1))
  Engine MyISAM;

 

If the table already exists, use these commands.

alter table MyTable Engine MyISAM;
alter table MyTable add fulltext index (Column1);

 

Use the show indexes in table_name command to verify the table now has a fulltext index. In this example, MyTable index type is fulltext.

Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment
MyTable 0           PRIMARY   1             column1      NULL       1            NULL      NULL    YES    FULLTEXT

 


Select modes

When selecting data from a table, there are a few different modes that can be used.

  • Natural language
  • Boolean mode
  • Query expansion

Natural language is the default mode, and does a simple or search. In this example, every record from the table that contains Super or man will be returned.

SELECT Column1 
FROM MyTable 
WHERE match (Column1) 
AGAINST ('Super man');

 

Boolean mode with the + characters is strict, and will only return results that contain every keyword used in the search. In this example, only records that contain both Super and man will be returned.

SELECT Column1 
FROM MyTable 
WHERE match (Column1) 
AGAINST ('+Super +man' IN BOOLEAN MODE);

 

Query expansion is what you would usually use in a modern search engine. This mode searches the database twice. First, every article that contains super or man is found, and all of the relevant keywords from every article are put into cache. Then, the database is searched again, using all of the keywords in the cache. The results are sorted by relevance. This makes it possible to search for "presidents of the United States" and get results for Barack Obama, Bill Clinton, and George Bush.

SELECT Column1 
FROM MyTable 
WHERE match (Column1) 
AGAINST ('Super man' WITH QUERY EXPANSION);

 


PHP (natural language, query expansion)

When using natural language or query expansion, selecting the data from the table in PHP is a farily straight forward process. You simply get create a variable for the user data, and then use the variable in the search.

$searchString = $_POST['searchString'];

foreach($con->query("SELECT Column1 FROM myTable WHERE match (article_title) AGAINST ('$SearchString' with query expansion)") as $row) 
{						
	echo $row['Column1'];

}

 

Boolean

Doing a boolean search requires a couple additional variables. In order for the select statements to function properly, the unique data being searched must be in a format like this: '+apple +banana +orange'. 

$preg_replace is used to add the + character to whitespace.

$mySearchString adds a single + character before the first word.

$SearchString = $_POST['SearchString'];

$pregSearchString = preg_replace('/\s+/', ' +', $SearchString);

$mySearchString = "+" . $pregSearchString;

foreach($con->query("SELECT Column1 FROM myTable WHERE match (article_title) AGAINST ('$SearchString' with query expansion)") as $row) 
{						
	echo $row['Column1'];

}

 

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments