Edit Question - KnowledgeHub

Title Body

I’m trying to do a full-text search in three seperate tables and sort the results by relevancy. During my searches for the answer, I found out that I can’t use fulltext search in multiple tables. So I added a separate fulltext index for each column I want to search. Now the problem is that I can do the search but I can’t do sorting as I would like to. Here’s my tables: CREATE TABLE books ( bookID int(11) NOT NULL AUTO_INCREMENT, title varchar(300) NOT NULL, authorID int(11) NOT NULL, FULLTEXT KEY title (title) ) CREATE TABLE IF NOT EXISTS authors ( authorID int(11) NOT NULL AUTO_INCREMENT, authorNamevarchar(200) NOT NULL, FULLTEXT KEY authorName(authorName) ); CREATE TABLE IF NOT EXISTS chapters ( chapterID int(11) NOT NULL AUTO_INCREMENT, bookID int(11) NOT NULL, content longtext NOT NULL, FULLTEXT KEY content (content) ); And here is my MySQL query where I’m stuck: SELECT *, MATCH(books.title) AGAINST('$q') as tscore, MATCH(authors.authorName) AGAINST('$q') as ascore MATCH(chapters.content) AGAINST('$q') as cscore FROM books LEFT JOIN authors ON books.authorID = authors.authorID LEFT JOIN chapters ON books.bookID = chapters.bookID WHERE MATCH(books.title) AGAINST('$q') OR MATCH(authors.authorName) AGAINST('$q') OR MATCH(chapters.content) AGAINST('$q') ORDER BY ???? DESC Now with this query I can do sorting by titles, authors or contents. What I want to do is, get the relevancy for all the three columns together and order the results by that. And, yes I’m aware of other search engines like Lucene or Sphinx, but I’m not planning to use them now.

Tags (comma-separated) Cancel