MySQL Forums
Forum List  »  Full-Text Search

Full text in search with 3 tables and inner join
Posted by: Steve Spiers
Date: July 11, 2007 02:28AM

I have been trying to changes a search query that uses LIKE to do its Keyword Searching part. I want to add full text boolean searches on the keywords fields, namily the title and description of the row.

Im reasonably proficient at mysql, but definately no expert. I may be looking at this in the wrong way, im not sure. Any help would be appreciated.

This search works without the boolean. I am trying to add the boolean to this search. Two inherant problems i have noticed.

1. Boolean searches dont like to have %% in them for a full search on no fields.

2. The boolean search requires it to start with SELECT. Making it difficult to merge into this sql.

Problem is, 3 tables all INNER JOINED. Everytime i get the boolean working the rest stops working.

I need to the search to work on ALL the current fields plus the KEYWORD field as boolean, instead of LIKE.

My thoughts after all my headaches of redoing this, are that the boolean needs to go into a search within the search.

All i need is that aspirin to get rid of the headache so i can clearly see whats going on, i know my sql, but as can be seen i am no expert.


SELECT

module_courseitems.*,

module_courseschedule.*,

system_regions.*

FROM

module_courseitems

INNER JOIN

system_regions

ON (module_courseschedule.schedule_region=system_regions.category_id)

INNER JOIN

module_courseschedule

ON (module_courseschedule.course_id=module_courseitems.course_id)

WHERE

module_courseitems.merchant_id LIKE '%%'

AND system_regions.nleft >= '1'

AND system_regions.nright <= '336'

AND module_courseitems.merchant_id LIKE '%%'

AND module_courseitems.course_code LIKE '%%'

AND module_courseitems.course_category LIKE '%%'

AND module_courseschedule.schedule_date LIKE '%%'

AND module_courseschedule.schedule_times LIKE '%%'

AND module_courseschedule.schedule_method LIKE '%%'

AND

THIS IS WHERE THE OLD SEARCH KEYWORDS CODE WAS, I WANT TO ADD THE FOLLOWING INTO HERE SOMEHOW.

THIS CODE WORKS BY ITSELF, THE ABOVE CODE WORKS BY ITSELF, I WANT THE TWO TO HAVE SEXORS (BE ONE SEARCH) AND MAKE BABIES (GIVE RELEVANT RESULTS)

SELECT

course_id, course_code, course_name, course_descript, course_detail,

MATCH (course_name, course_descript, course_detail)

AGAINST ('$string')

AS score

FROM

module_courseitems

WHERE MATCH (course_name, course_descript, course_detail)

AGAINST ('$string' IN BOOLEAN MODE) LIMIT 0, 3

After doing alot of testing i found out that the boolean works with this


AND MATCH (course_name, course_descript, course_detail)

AGAINST ('$string' IN BOOLEAN MODE) LIMIT 0, 3


but wont work with the AS score. It would be nice to add the score part as well.

Options: ReplyQuote


Subject
Views
Written By
Posted
Full text in search with 3 tables and inner join
9561
July 11, 2007 02:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.