Full text in search with 3 tables and inner join
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.