• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

full text search

Hi,
I want to do full text  searching on my table.
If some one searches for '2,23' it should not get any result.
But if it searches '38,13,10' / '12,23' / just '2' than it should get result.
Please let me know the query if possible. If any changes has to be done on table level please even let me know tht

CREATE TABLE `document` (
  `document_id` int(11) DEFAULT NULL,
  `category_id` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `search_category` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

----------------------------------
  document_id   |      category_id
-----------------------------------
     1            | 38,10,49,13,16
------------------------------------
     2            | 12,23
------------------------------------
     7            | 2
0
Insoftservice
Asked:
Insoftservice
  • 12
  • 11
5 Solutions
 
johanntagleCommented:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

SELECT * FROM document WHERE MATCH (category_id)
AGAINST ('+12 +23' IN BOOLEAN MODE);
0
 
InsoftserviceAuthor Commented:
its not accepting two characters
wht setting has to be done in it.
0
 
johanntagleCommented:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html


You need to set in your mysql config (usually my.cnf)
ft_min_word_len=2

Then restart mysql server
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
InsoftserviceAuthor Commented:
hi @johanntagle:

thx but i can change the setting of the server.
So, i want to change the setting only for the specific db.
Is it possible.
0
 
InsoftserviceAuthor Commented:
hi,
As its on shared server. i am trying to do it via php, or if possible for specific db from the server.
0
 
johanntagleCommented:
No it's not as it is a server-wide setting.
0
 
johanntagleCommented:
It looks like you are trying to use the full-text index as an alternative to a join table.  May I suggest that you use a join table instead:

CREATE TABLE `document` (
  `document_id` int(11) DEFAULT NULL,
  /*other document columns but NOT category_id here */
) ENGINE=MyISAM DEFAULT CHARSET=utf8

create table 'documents_categories' (
  document_id int(11),
  category_id int(11)
)

Then your select statement is simply
select d.* from document d inner join documents_categories c on d.document_id=c.document_id
where c.category_id in (12,23);

To make it faster, create an concatenated index for the two columns in documents_categories.  If you frequently search by category_id, then you put category_id first in the index creation i.e. (category_id, document_id)

What I described above is really the standard and most efficient way to handle many to many relationships in a relational database.  I've noticed quite a few people try to do it a different way to maybe "simplify" their code but they don't realize the "more simple" code is actually terribly inefficient.  Most databases are highly efficient in handling these types of relationships so we should just let the database do what they were made to do.
0
 
InsoftserviceAuthor Commented:
hi,
its not worth for this part.
currently i just inserted default zero value in the categoryid so as to get more than min word
0
 
johanntagleCommented:
Well I still don't recommend it but whatever works for you =)
0
 
InsoftserviceAuthor Commented:
hi,

may i know how to do partial search of text.

like if word is without

match (col1,col2,col3) against ('*without* in boolean mode')

match (col1,col2,col3) against ('*out* in boolean mode')
match (col1,col2,col3) against ('*with* in boolean mode')

does not works

0
 
johanntagleCommented:
So do you need anything else or can we close this question already?  Thanks
0
 
johanntagleCommented:
Do you get an error or you don't get the expected matching roles?
0
 
johanntagleCommented:
I see the problem now.  IN BOOLEAN MODE should be outside the quotation marks

like this: match (col1,col2,col3) against ('*with*' in boolean mode)

not: match (col1,col2,col3) against ('*with* in boolean mode')
0
 
InsoftserviceAuthor Commented:
no actually i did not got the result of my above question , i am trying to go round the way instead of categoryid i am trying to go via category title  text search now, where id is changes to title and tilte would be saved with comma like
text search, category info , details . and accordingly search would be done
0
 
InsoftserviceAuthor Commented:
hi,
srry printing mistake
$search_value = 'with';
 MATCH(col1,col2col3) AGAINST ('*".$search_value."*'  IN BOOLEAN MODE)

and i did not notice that u have already provided the answer
0
 
johanntagleCommented:
Okay so it works now?  Just let me know if you need help on something else.
0
 
InsoftserviceAuthor Commented:
no
its not working

i had miss ' while pasting in my first comments.
if i remove * before $search_value ie AGAINST ('".$search_value."*'  IN BOOLEAN MODE)

it works only for 'with' for the word without.
but i even want if client tries to search with word 'out'
0
 
johanntagleCommented:
http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

Sorry, misunderstood this feature too.
0
 
InsoftserviceAuthor Commented:
ya,
tht what.

if the word id "searchtext"

and if i search for 'search'

AGAINST ('search*'  IN BOOLEAN MODE) it works

but

AGAINST ('*text'  IN BOOLEAN MODE) it  does not works

may i know how to get even with word 'text'
0
 
johanntagleCommented:
As what I quoted in the previous post says, you can't.  
0
 
InsoftserviceAuthor Commented:
no,
actually i had done this section for my previous clients where same code had been written for search there it worked i don't remember what changes i did in the specific tables.

*text* =>it worked for old projects
0
 
johanntagleCommented:
Well, sorry but I can't help you with that.  The documentation clearly says "Words match if they begin with the word preceding the * operator."
0
 
InsoftserviceAuthor Commented:
thx for ur time and help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now