CODEWITHSUNDEEP
mysqlfull-text-searchsql-like
Norman Bird
Norman Bird

Reputation: 682

Fulltext search with mysql ignoring # character

I wrote a simple diary/journal online application at personaldreamjournal.com. You can login in demo mode to test it using name guest and password user.

It uses the LIKE to do fulltext searching on words in the diary. I wanted to add the ability to allow hashtags for easy tagging of entries. like if someone enters #workout, it returns all the entries with hashtag #workout. But evidently not that easy as it appears to ignore the hashtag. (no idea why they would ignore ANY character in a text box??, but so is life).

So all entries with the word workout is returned as # is ignored. I just want confirmation, from what I have searched so far, as nothing specifically mentions the # character.

So im assuming # is a special character that is ignored for some reason? and that for me to change it I have to alter the MYSQL configuration somewhere? Where exactly for the # character?

Any help confirming what im saying is true, and how to allow for the #character to be counted in return results, is appreciated.

UPDATE: below is the actual code I was using. so I wasn't using LIKE

  $query_Recordset1 = "SELECT *, MATCH(`scroll`.text) AGAINST (+'$searchstring_Recordset1'     IN BOOLEAN MODE) AS score FROM scroll WHERE MATCH(`scroll`.text) AGAINST('+$searchstring_Recordset1' IN BOOLEAN MODE) having score > 0.2 AND user = '$username' ORDER BY score DESC";

Upvotes: 0

Views: 1662

Answers (1)

James
James

Reputation: 3805

Norman, using the LIKE keyword is not using full text searching. You have to set the text field to have a fulltext index. (MyISAM tables, I believe) Then, do something like this:

Select * From table Where (MATCH (field) AGAINST ('keyword' IN BOOLEAN MODE) > 0);

This will give you all results that match.

If you wanted them with a ranking order as well as rank returned, you can do:

Select *, (MATCH (field) AGAINST ('keyword' IN BOOLEAN MODE)) As rank From table Where (MATCH (field) AGAINST ('keyword' IN BOOLEAN MODE) > 0) Order By (MATCH (field) AGAINST ('keyword' IN BOOLEAN MODE)) Desc;

Using the full text search multiple times will not slow down your query, I've read, as MySQL sees that it's the same search and just uses the same results.

Also, if you just want an answer for using the LIKE keyword, we'll need to see your original query.

EDIT

It looks like this person had the same issue as you and it was resolved.

MySQL Full-Text search for hashtags (including the # symbol in index)

So using the full text search plus this should be what you need, it seems.

Upvotes: 4

Related Questions