We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

full text search

Medium Priority
461 Views
Last Modified: 2013-12-08
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
Comment
Watch Question

Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
its not accepting two characters
wht setting has to be done in it.
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.

Author

Commented:
hi,
As its on shared server. i am trying to do it via php, or if possible for specific db from the server.
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2012

Commented:
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.

Author

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
Top Expert 2012

Commented:
Well I still don't recommend it but whatever works for you =)

Author

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

Top Expert 2012

Commented:
So do you need anything else or can we close this question already?  Thanks
Top Expert 2012

Commented:
Do you get an error or you don't get the expected matching roles?
Top Expert 2012

Commented:
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')

Author

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

Author

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
Top Expert 2012

Commented:
Okay so it works now?  Just let me know if you need help on something else.

Author

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'
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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'
Top Expert 2012

Commented:
As what I quoted in the previous post says, you can't.  

Author

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
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thx for ur time and help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.