Full-text searching two tables

[Tachyon]

I'm trying to create a full-text search engine for my blog.  I read the tutorial, and I grasp the basics of how to search a single table.  However, when a user enters a search term, I want to search both the table of blog posts (vsns_news) and the table of comments (vsns_comments) for the term and treat the results on equal footing.  That is, I don't want to display first the results from blog posts and then the results from comments: they should intermingle in order of decreasing relevancy.

 

However, I can't for the life of me come up with a query that will search both tables and return a combined result set for them.  Is there a way to use just one query?

[cooldude832]

Just a stab in the dark but on the line FROM Table name try FROM TABLE1 and TABLE 2 WHERE Field Like $criteria

fyi though this will require that the field name be the same in both tables

[Tachyon]

Just a stab in the dark but on the line FROM Table name try FROM TABLE1 and TABLE 2 WHERE Field Like $criteria

fyi though this will require that the field name be the same in both tables

The field name is not going to be the same in both tables--in fact, I am searching against multiple fields.

 

This is one way I tried it:

SELECT *, MATCH (vsns_news.heading, vsns_news.content) AGAINST ('" . $term . "') as news_score, MATCH (vsns_comments.comment) AGAINST ('" . $term . "') as comment_score FROM vsns_news, vsns_comments WHERE MATCH (vsns_news.heading, vsns_news.content) AGAINST ('" . $term . "') OR MATCH (vsns_comments.comment) AGAINST ('" . $term . "') AND vsns_comments.queue = '0' AND vsns_news.queue = '0' ORDER BY news_score, comment_score DESC LIMIT $from, $results

 

I know it's a horrific statement; at this point I was just fiddling with things to see if I could get a good result.

[teng84]

joining is what you need

 

http://w3schools.com/sql/sql_join.asp

[Tachyon]

joining is what you need

 

http://w3schools.com/sql/sql_join.asp

I suspected that a join may hold the answer.  I've never worked with complex joins before, so I'm not sure how it will help me out or what sort of join I should use.  I will look into it, but if anyone cares to elaborate, that'd be really helpful.

[teng84]

ok join means join the content of the first table form the secon

 

but you need to have the relationship on each table

 

sample the first table id relates the second teble id jus a brief idea 

[Tachyon]

I'm aware of the purpose of a table join.   I'm just not sure how I would format my MATCH () ... AGAINST () statements in an SQL query with a table join ... it's a sight more complex than I've ever done.

 

The news table, vsns_news, has an ID field and vsns_comments has an article_id field that corresponds to the matching ID in a record in vsns_news.  So matching up the tables is no problem.

[teng84]

match ??

i dont hear about that

against?

 

[mmarif4u]

@teng84 For match read this article:

Its is helpful when searching multiple fields in db table with fulltext search.

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html

[teng84]

thanks SASASASASSASASASA