[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL fulltext search joining tables

Posted on 2011-10-28
2
Medium Priority
?
530 Views
Last Modified: 2012-05-12
Hi,

I have a fulltext search on one of my tables but I want to also search on another table that is linked to another table (I know that doesn't make any sense yet)

I have 3 tables

tblblogarticle
articleID
posterName
articleTitle
articleContent

tblarticletags
articleID
tagID

tbltags
tagID
tagName

I have a fulltext search running on the tblarticle table that looks like this:
mysql_query('SELECT articleID, posterName, articleTitle, articleContent FROM tblblogarticle WHERE MATCH(articleTitle, articleContent) AGAINST ("'.$phrase.'"));

This is working ok, but I woud like to expand my search to include the article tags associated with each article.  The link is each article has several entries in the tblarticletags table that stores the articleID and tagID fields.  The tbltags table contains the tagID and tagName.  I want to search on the tagName with reference back to the original article to display the correct search results.

I guess I am going to have to use joins to do this, but am not sure how to construct it?  Any advice is welcomed.

Kind Regards,

John
0
Comment
Question by:john-formby
2 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 37044198
Usually tags are exact matches so I assume there's no fulltext index on tbltags:

SELECT articleID, posterName, articleTitle, articleContent
FROM tblblogarticle
WHERE MATCH(articleTitle, articleContent) AGAINST ("'.$phrase.'")
OR articleID in (select articleID from tblarticletags artgs join tbltags tgs on (artgs.tagID=tgs.tagID) where tgs.tagName="'.$phrase.'" )

ensure that tbltags.tagName is indexed.

If you want fulltext for tagName, then just replace the filter with MATCH, though you might encounter performance problems with many MATCH filters.  You might want to restructure your database where all fields to be searched are in one table, so you can make one full text index for the combined columns.
0
 
LVL 14

Author Comment

by:john-formby
ID: 37049558
Thanks for the help, that works perfectly.

Kind Regards,

John
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month17 days, 14 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question