FreeKB - How to use fulltext index search in MariaDB and PHP
How to use fulltext index search in MariaDB and PHP

Home > Search > How-to


Let's say you have a table with the following columns.

id name
1 Super Man
2 Bat Man
3 Wonder Woman

 

Let's consider the following SQL query. In this example, only the Super Man record will be returned.

SELECT *
FROM super_heros
WHERE name
LIKE 'Super'

 

Updating your table to be a fulltext index resolves this issue. Fulltext has a feature that searches the database twice. First, every article that contains Super is found, and then both "Super" and "Man" are put into cache. Then, the database is searched again, finding all of the article that contain "Super" or "Man". In this way, searching with "Super" will return both Super Man and Bat Man.

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

CREATE TABLE super_heros(name varchar(100),FULLTEXT(name)) ENGINE=MyISAM;

 

If the table already exists, use these commands.

alter table super_heros Engine MyISAM;
alter table super_heros add fulltext index (name);

 

Use the show indexes in super_heros command to verify the name column in the super_heros table is a fulltext index.

Table       Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment
super_heros 0           PRIMARY   1             id           A          1644         NULL      NULL    YES   BTREE
super_heros 1           ft_name   1             name         NULL       1644         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 * 
FROM super_heros 
WHERE match (name) 
AGAINST ('Super');

 

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 * 
FROM super_heros 
WHERE match (name) 
AGAINST ('+Super' 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 * 
FROM super_heros 
WHERE match (name) 
AGAINST ('Super' 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 6f0f8 in the box below so that we can be sure you are a human.




Comments