cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 I have 3 tables that all relate back to a user Users Table (user data email, address, phone etc.) Relationships (user-user link dates, etc.) Employment records (who they are employed by, dates, position etc.) The goal is to full text search any field in any table pertaining to a user so if the keywords are oranges, Florida and farmer it will match a person who lives in Florida, worked in Florida, worked with oranges or is a farmer. What is the best way to accomplish such a complex query? There will be around 50+ fields that will be looking for "full text" Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/ Share on other sites More sharing options...
mkoga Posted March 29, 2008 Share Posted March 29, 2008 One way to handle such a complex search is to index them with lucene. The zend framework has an implementation of it: http://framework.zend.com/manual/en/zend.search.lucene.html along with tutorials. Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/#findComment-503821 Share on other sites More sharing options...
mwasif Posted March 29, 2008 Share Posted March 29, 2008 You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/. Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/#findComment-504004 Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Author Share Posted March 29, 2008 You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/. I sure can fulltext across multiple tables I may need to use multiple queries but it can be done Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/#findComment-504008 Share on other sites More sharing options...
fenway Posted March 29, 2008 Share Posted March 29, 2008 You can not have multiple table fulltext index in mysql. You might need to look at http://www.sphinxsearch.com/. I sure can fulltext across multiple tables I may need to use multiple queries but it can be done It can't be done without multiple queries... but that's not going to "use" an index. Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/#findComment-504014 Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Author Share Posted March 29, 2008 well Ideally I want to have the keywords found be highlighted in the search results so if you matched former job it would say former job: <b> Orange</b> Picker but the thing is there is about 50 fields not to mention there can be hundreds of relationships and jobs to be queried off for each user row. My original thought was each time a usersID is hit in any table to recompile a field for the user that contains their "Info" a formatted field that would then be full searched. And then if that hits full text I would then use php to treat it as a csv file for say. Is that a good idea or no? Link to comment https://forums.phpfreaks.com/topic/98441-fulltext-keyword-search-across-multiple-tables-should-i-do/#findComment-504022 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.