full text search

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

its not accepting two characters
wht setting has to be done in it.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

ASKER

hi,
As its on shared server. i am trying to do it via php, or if possible for specific db from the server.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

ASKER

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

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

ASKER

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

So do you need anything else or can we close this question already? Thanks

Do you get an error or you don't get the expected matching roles?

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

ASKER

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

ASKER

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

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

ASKER

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'

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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'

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

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.