Questions on full text searching-- need to search multiple tables -- Help!

[CountryGirl]

I have 9 tables joined into my search. At the moment I'm using the code below to return the results.

 

WHERE asmnt_parcel.Account LIKE '{$search}' OR asmnt_parcel.OwnersName LIKE '{$search}' OR asmnt_parcel.ParcelID LIKE '{$search}' OR asmnt_legal.Legal LIKE '{$search}'

 

I have more tables that I need to be able to pull records from in a search though, so obviously the above method won't work too well. Thus, I'm trying to do full text searching.

 

I can full text search from one table, but when I try to add in a second table to be full text searched from, it won't work.

 

This query works perfect:

 

"SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt
			 FROM appr_agland
			 LEFT JOIN appr_resident
			 ON appr_agland.Account=appr_resident.Account
			 WHERE MATCH (appr_agland.Account) AGAINST('$search')";

 

But, as soon as I try to add in the second table, kind of like this query below (note bolded part), it doesn't work:

 

"SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt
			 FROM appr_agland
			 LEFT JOIN appr_resident
			 ON appr_agland.Account=appr_resident.Account
			 WHERE MATCH (appr_agland.Account) AGAINST('$search') [b]OR MATCH (appr_resident.Account) AGAINST('$search')"[/b];

 

How can I make it so that I can full text search from multiple tables? Or, is there a better way to search from multiple tables?

 

Thanks for any & all help!!

Qadoshyah

[fenway]

No, you can't span multiple tables with fulltext -- but you can run multiple queries and combine the results.

[CountryGirl]

No, you can't span multiple tables with fulltext -- but you can run multiple queries and combine the results.

 

Would you mind explaining just a little how I would do that?

 

Thanks!

 

Qadoshyah

[fenway]

Just combine the results, in application code, from each table's fulltext search.  Otherwise, you're stuck with (a) writing your own or (b) a third-party solution like Sphinx.

[CountryGirl]

Just combine the results, in application code, from each table's fulltext search.  Otherwise, you're stuck with (a) writing your own or (b) a third-party solution like Sphinx.

 

Like making multiple SELECT statements and having them all join into one results page?

 

Qadoshyah