CODEWITHSUNDEEP
mysqlfull-text-search
kirby
kirby

Reputation: 4041

FULLTEXT mysql search not working

I have a query that gets comments from a database and displays them based on what the user typed in.

PHP:

$query=mysql_query("SELECT * FROM comments WHERE MATCH (comments) AGAINST ('%$user_text%')");
    while ($row=mysql_fetch_assoc($query)){
        echo "<div>$row['comment']</div>";
    }

This doesn't return any values. It may be that the comments column in my mysql database isn't set to FULLTEXT. i ran this script ALTER TABLE comments ADD FULLTEXT(comment); but i can't verify that the index is indeed FULLTEXT

UPDATE (after verifying how table was created):

CREATE TABLE `comments` (
`id` int(11) NOT NULL...

 PRIMARY KEY (`id`),  //these are the last 3 lines
 FULLTEXT KEY `comments` (`comment`)
) ENGINE=MyISAM AUTO_INCREMENT=93 DEFAULT CHARSET=latin1

Upvotes: 0

Views: 2828

Answers (2)

Mike Purcell
Mike Purcell

Reputation: 19999

Think I got it. In your original query you are not specifying an optional modifier, which according to MySQL, means it defaults to IN NATURAL LANGUAGE MODE. And according to some docs I have read, and my own testing, IN NATURAL LANGUAGE MODE will only match 'whole' words.

If you want to do partial string matching, you will have to use the IN BOOLEAN MODE modifier instead. However, there is a limitation in that MySQL cannot search using wildcards prepended to a search string, because they are indexed left-to-right, not right-to-left. So to get your search working, try this:

SELECT * FROM comments WHERE MATCH (comments) AGAINST ('$user_text*' IN BOOLEAN MODE)"

You can read up on boolean fulltext searches here.

Also, you should read up on mysql_real_escape_string to sanitize variables going into your queries to protect from SQL injection attacks.

Upvotes: 3

cEz
cEz

Reputation: 5062

To verify the table:

SHOW CREATE TABLE comments;

This will show you the statement used to recreate the table, including indexes. Also, you specified the 'comment' field in the index, yet the 'questiontitle' in the MATCH condition.

Try:

"SELECT * FROM comments WHERE MATCH (questiontitle) AGAINST ('$user_text')"

or:

"SELECT * FROM comments WHERE MATCH (questiontitle) AGAINST ('$user_text' WITH QUERY)" EXPANSION

ADDED: You also have a possible typo

($row=mysql_fetch_assoc($query)

.. missing the closing parenthesis -> ($row=mysql_fetch_assoc($query))

Upvotes: 0

Related Questions