Ad:

PHP Discussion Thread
Unsolved
Views: 4383

Sep 26th, 2006

Full-text search on two tables

Expand Post »

Hi, I managed to use the full-text search on a single table (product) and would like to search two tables (product and article) simultaneously, when user enter a keyword. Can anyone help?

Thanks

Similar Threads

zippee

Reputation Points: 10

Solved Threads: 7

Posting Whiz in Training

Offline

294 posts
since Jan 2005

Permalink

Sep 26th, 2006

Re: Full-text search on two tables

Have been wondering the same. I believe you can only do a full text search on selected fields within a single table. In the past when I was in dire need of this feature and didn't have time to experiment, I did a brute force method where I ran a full text search on one table, a second full text search on a second table, and then merged the resultsets.

Of course, you can do a full text search on the article table and left join in the product table (without searching it).

cscgal

Administrator

Staff Writer

Reputation Points: 1422

Solved Threads: 162

The Queen of DaniWeb

Offline

13,645 posts
since Feb 2002

Permalink

Sep 26th, 2006

Re: Full-text search on two tables

em... interesting. I was thinking of storing results from both tables (separate full-text search as you mentioned) in array, then sort them based on their scores, and display them. Not sure this may cause a long delay... any thought?

zippee

Reputation Points: 10

Solved Threads: 7

Posting Whiz in Training

Offline

294 posts
since Jan 2005

Permalink

Sep 26th, 2006

Re: Full-text search on two tables

Yes, that's what I've done in the past. It did put a heavy load on the server.

cscgal

Administrator

Staff Writer

Reputation Points: 1422

Solved Threads: 162

The Queen of DaniWeb

Offline

13,645 posts
since Feb 2002

Permalink

Mar 5th, 2010

Re: Full-text search on two tables

i hve a problem in searching a two tables..?

mr.adrian

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2010

Permalink

Apr 23rd, 2010

I attempted this today with unexpected results.

Sorry to revive such an old thread, but I rather hoped that since newer versions of MySQL are now in wide circulation an answer will be available.
I tried the following:

PHP Syntax (Toggle Plain Text)
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

then I tried:

PHP Syntax (Toggle Plain Text)
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

I get a total of seven results returned from these two queries. However, when I tried the following I got a little over 62K results.

PHP Syntax (Toggle Plain Text)
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
       OR MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1
SELECT pd.products_id, pd.products_name, p.products_price
	FROM products_description pd, products p 
	WHERE MATCH (p.products_model) AGAINST ('+Speaker +Mic Miner' IN BOOLEAN MODE)
       OR MATCH (pd.products_name,pd.products_description) AGAINST ('"Speaker Mic" >Miner' IN BOOLEAN MODE)
	AND p.products_id = pd.products_id 
	AND p.products_status = 1

Does anyone have any idea how to accomplish this?

jsalis

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Apr 2010

Permalink

Apr 23rd, 2010

Full Text Search Multiple Tables Solved.

I found the solution to this. I was apparently very close. The MATCH AGAINST OR MATCH AGAINST was the way to go, but I was not joining the tables correctly. Here is again with LEFT JOIN instead and relevancy scoring.

PHP Syntax (Toggle Plain Text)
SELECT pd.products_id, pd.products_name, p.products_price, MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE) AS score
      FROM products_description pd
		LEFT JOIN products p
		ON pd.products_id = p.products_id 
      WHERE MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      AND p.products_status = 1
      ORDER BY score DESC
SELECT pd.products_id, pd.products_name, p.products_price, MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE) AS score
      FROM products_description pd
		LEFT JOIN products p
		ON pd.products_id = p.products_id 
      WHERE MATCH (p.products_model) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      OR MATCH (pd.products_name,pd.products_description) AGAINST ('LAA 0209' IN BOOLEAN MODE)
      AND p.products_status = 1
      ORDER BY score DESC

jsalis

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Apr 2010

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: How to delete related table records?

Next Thread in PHP Forum Timeline: trying to send form data to database if radio is checked

DaniWeb Message

Cancel Changes

User Name
Password