FULLTEXT search over multiple tables??

[seodevhead]

Is it possible to have one FULLTEXT search performed on columns from multiple tables in the same DB? I have a lot of primary->foreign key relationships that need linked and searched in one single FULLTEXT search?

Here is my scenario...


TABLE cars {
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
brand_id SMALLINT UNSIGNED NOT NULL,
color VARCHAR(250) NOT NULL,
description TEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT (color, description)
}

TABLE car_brands {
brand_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
brand_name VARCHAR(250) NOT NULL,
PRIMARY KEY (brand_id),
FULLTEXT (brand_name)
}


As you can see, cars.brand_id=car_brands.brand_id in a query. The problem is, I want one single FULLTEXT search to be performed on the car brand, color and description. But if I do a fulltext search on just the 'cars' table, I can't effectively search the brand names because this table only has brand_id which is a number that links to 'car_brands' table (which contains the linked brand_name). So how do I go about including 'car_brands.brand_name' with 'cars.color' and 'cars.description' in one single FULLTEXT search? Thanks for your help! :)

[MarioPro]

Well, you better try Joining tables first and do the search after. See below (not tested):



$query="SELECT DISTINCT(cars.brand_ID), cars.* FROM cars ";
$query.="LEFT JOIN car_brands ON cars.brand_ID=car_brands.brand_ID ";
$query.="WHERE cars.description LIKE '%Your text from input%' ";
$query.="ORDER BY cars.brand_ID ASC";
$result=mysql_query($query) or die("Can't Search: ".mysql_error());
while($fields=mysql_fetch_array($result)){
print "Brand ID: ".$fields["brand_ID"]." Description: ".$fields["description"]." Brand name: ".$fields["brand_name"]."<br>";
}