Bootstrap FreeKB - PHP - fulltext index search with Microsoft SQL Server
PHP - fulltext index search with Microsoft SQL Server

Updated:   |  PHP articles

The following commands can be issued to create a full-text search in SQL Server.

Select the database that is being used.

use database_name

 

Create a fulltext catalog. Replace catalog_name with a name for your catalog, such as MyCatalog.

create fulltext catalog catalog_name

 

Verify that the catalog has been created.

select * from sys.fulltext_catalogs

 

Also use this command to confirm that full-text is installed. Under No column name, if there is a 1, full-text is installed. If there is a 0, full-text is not installed. If full-text is not installed, run the SQL Server installed, and select full-text.

select serverproperty('IsFullTextInstalled')

 

Alter the ID column of the table, adding a constraint to the table, such as MyConstraint.

alter table table_name add constraint constraint_name unique (id)

 

If a mistake is made, use the following command to remove the unique constraint from the table.

alter table table_name drop constraint constraint_name

 

Use the following command to verify that the unique constraint has been applied to the correct database, table, and column.

select * from information_schema.table_constraints where constraint_type='unique'
select * from information_schema.constraint_column_usage

 

Use the create full index command to identify which columns you want to be searchable by the search engine.  In this example, use the same table name and contraint name that was used in the previous alter table command. Use the catalog_name that was used in the previous create fulltext catalog command.

create fulltext index on table_name (column1, column2, column3) key index constraint_name on catalog_name

 

If you make a mistake when creating the fulltext index, you can remove the fulltext index using the drop command.

drop fulltext index on table_name

 

Stops words, such as if and the can cause issues at first, so let's disable the stop words.

alter fulltext index on table_name set stoplist = off

 

To text fulltext search, issue the following command.  Only records where the Title contains both the keyword test and one will be returned.  This is perfect.

select * from table_name where contains ( (column1,column2,column3), 'test+one')

 

Let's say we have a PHP website with a search engine, where the search engine checks for records in our MS SQL table. If we try using the command select * from content where contains (title, 'test+one') in PHP, we will probably receive the following error.

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 7630 [code] => 7630 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Syntax error near 'one' in the full-text search condition 'test one'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Syntax error near 'one' in the full-text search condition 'test one'. ) )

 

This error appears because PHP places the + character between keywords.  In this example, the + character would be placed in test+one. SQL Server is unable to process the + character, which is why this error appears. To resolve this, we need to replace the + character with the ~ character, so that the ~ character is placed between test~one.  SQL Server uses the ~ character to distinguish between keywords.  The following code replaces the + character with the ~ character.

//replace the + characters in the URL with a whitespace
$first_preg = preg_replace ('/\+/', '', $_POST['search']);

//replace the whitespaces in the URL with an ~ character
$second_preg = preg_replace ('/ /', '~', $_POST['search']);

$sql = "select * from tablename where contains (title, '".$second_preg."') "; 

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


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