Full text search from two tables but finally one!

saidbakr

Hi,

I have a two tables one is articles and the other is comments. Of course the primary key in articles which is named id is set to be a forign key in comments to handles the related comments of a specific article. The general structure of the two table is summerised below:
articles
id *
title FULLTEXT
content FULLTEXT

comments
id *
article_id
content FULLTEXT

FULLTEXT is set well and there is no problem to perform it. What I need to know is how to search the full text fields in the two tables to get the the id of the article that has the search value in articles.title or articles.content or comments.content?

In other word, I need the query result to give the id of article that has the search string in either articles (title and content) or comments content.

Kudose

SELECT a.id FROM articles AS a, comments AS c 
WHERE a.title LIKE '%string%' 
OR a.content LIKE '%string%' 
OR c.content LIKE '%string%'

Maybe?

saidbakr

Hi,

I need fulltext search using match() against. I just need the id of articles regardless its being from articles table or comments (article_id) table.

phaseonemedia

perhaps this is what you need?

select a.*, c.* from articles a, comments c where match(a.title,a.content,c.content) against('".$keywords."')

saidbakr

Hi,

MySQL Said : #1210 - Incorrect arguments to MATCH

phaseonemedia

hmmm,

select a., c. from articles a, comments c where match(a.title,a.content) against('".$keywords."') or match(c.content) against('".$keywords."')

select a., c. from articles a, comments c where match(a.title) against('".$keywords."') or match(a.content) against('".$keywords."') or match(c.content) against('".$keywords."')

rulian

I think it's keyword againt fields

saidbakr

Hi,

I think it is not possible to make such a query. Initialy, I wanted to perform a fulltext search throught the articles tables and comments table to get the article id from both table, where the id is primary key in articles and it is demonstarted as article_id in comments table to act as a forign key.

Therefore, if the keyword is found in the comments table, the query will return the aricle_id as id .

I think this is impossible, so I decided to make two seperate queries for each table and then they may be handled through the application.