CODEWITHSUNDEEP
phpmysqlfull-text-search
Ankit Khatri
Ankit Khatri

Reputation: 251

Searching for phrases in a mySQL database

I have a mySQL database of words and phrases that is searchable. If the user inputs a long string, how do I search for any occurrences of phrases in my database that are contained in that string?

I have done it for single words but not for phrases.

Upvotes: 2

Views: 6058

Answers (5)

vfedorkov
vfedorkov

Reputation: 847

In stock MySQL you could use 

SELECT * from <my_table> WHERE phrase_column LIKE '%my search phrase%'

but it would be a VERY slow query as it will unable to use any index. And it doesn't supports any morphology.

If you have big enough phrase set you could hire phrase operator from extended query language in Sphinx to perform phrase search as described in http://sphinxsearch.com/docs/current.html#extended-syntax

Basically you could install Sphinx and create simple on-disk index which contains phrases from MySQL table you've mentioned. Later you could boost required phrases by adding rating to each document you index using additional integer column.

You could find quick Sphinx configuration guide at http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/ After firing up Sphinx you could connect to Sphinx and using phrase search:

SELECT * FROM <your_index_name> WHERE MATCH('"my search phase"');

Please note both single (string) and double (phrase operator) parentheses.

Sphinx will return you phrase IDs so you can retrieve them from MySQL in very fast way (it is usually select using primary key)

Another place to look is http://lucene.apache.org/ which is another great full-text search engine.

Upvotes: 1

Alexey Gerasimov
Alexey Gerasimov

Reputation: 2141

Here you go. This will take all of the text user entered, get separate words and look for any of those words (whole or part - pick which where clause you wnt)

Let's assume that $user_input is set from $_POST

$user_input = $_POST;
// try this one for sample data $user_input = "hi hello wh'at els;e is new select 1 from dual;";
$user_input_array = array_map('mysql_real_escape_string', explode(' ',$user_input));
$where_clause = "YOURCOL = '".implode("' OR YOURCOL = '", $user_input_array)."'";

echo $where_clause.'<br/>';

// if you want to use IN 
$where_clause = " YOURCOL IN ('".implode("', '", $user_input_array)."')";
echo $where_clause.'<br/>';

// if you want to use LIKE

$where_clause = "YOURCOL LIKE '%".implode("%' OR YOURCOL LIKE '%", $user_input_array)."%'";
echo $where_clause.'<br/>';

Results from above are:

YOURCOL = 'hi' OR YOURCOL = 'hello' OR YOURCOL = 'wh\'at' OR YOURCOL = 'els;e' OR YOURCOL = 'is' OR YOURCOL = 'new' OR YOURCOL = 'select' OR YOURCOL = '1' OR YOURCOL = 'from' OR YOURCOL = 'dual;'

YOURCOL IN ('hi', 'hello', 'wh\'at', 'els;e', 'is', 'new', 'select', '1', 'from', 'dual;')

YOURCOL LIKE '%hi%' OR YOURCOL LIKE '%hello%' OR YOURCOL LIKE '%wh\'at%' OR YOURCOL LIKE '%els;e%' OR YOURCOL LIKE '%is%' OR YOURCOL LIKE '%new%' OR YOURCOL LIKE '%select%' OR YOURCOL LIKE '%1%' OR YOURCOL LIKE '%from%' OR YOURCOL LIKE '%dual;%'

Upvotes: 0

TimWagaman
TimWagaman

Reputation: 118

If you're going to be searching the text often, then full text would be your best bet. Also, I assume that your column is varchar (not TEXT) or else LIKE would not work.

Upvotes: 0

Dan Crews
Dan Crews

Reputation: 3637

You're going to want to pull a FULLTEXT index on the fields to make it faster, but you can do

SELECT *
FROM Table
WHERE MATCH ('column_name') AGAINST ('here is the phrase')

Upvotes: 1

Mark Grey
Mark Grey

Reputation: 10257

Maybe use the fulltext features of mysql?

http://devzone.zend.com/26/using-mysql-full-text-searching/

This example uses Zend framework, but the SQL is the same.

There are some open source options that will setup a separate fulltext search server if your indexing/ranking needs are more complex.

Sphinx http://sphinxsearch.com/

Xapian http://xapian.org/

Upvotes: 1

Related Questions