One FULLTEXT search, multiple tables
Moderator: General Moderators
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
One FULLTEXT search, multiple tables
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? Any ideas? Thanks!
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
Mysql?
-NSF
Code: Select all
SELECT * FROM ref_table,other_table- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
There is a lot you are not proving us with seodevhead...
such as the REAL sql statement #1... as well as what fields are contained within each table.
a fulltext search though can ONLY be used with text, and varchar type fields, and you MUST specify the "FULLTEXT" command to each field that matches that type to be searchable...
So, could ya throw an outline of your tables to us? Cuz it sounds to me like you did not setup fulltext searching on them.
such as the REAL sql statement #1... as well as what fields are contained within each table.
a fulltext search though can ONLY be used with text, and varchar type fields, and you MUST specify the "FULLTEXT" command to each field that matches that type to be searchable...
So, could ya throw an outline of your tables to us? Cuz it sounds to me like you did not setup fulltext searching on them.
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
Here is my scenario...
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! 
Code: Select all
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)
}is this what you are looking for?
or, if you want to include the text searches...
or if you actually are only allowing them to specify, submit like % to = '$var'
Code: Select all
SELECT cars.color, cars.description,car_brands.brand_name FROM cars INNER JOIN car_brands ON car_brands.brand_id = cars.brand_idCode: Select all
SELECT cars.color, cars.description,car_brands.brand_name FROM cars INNER JOIN car_brands ON car_brands.brand_id = cars.brand_id WHERE car_brands.brand_name LIKE %$some_brand_var% AND cars.description LIKE %$some_description_var% AND cars.color = '$some_color_var'- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
sorry misunderstood...
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://www.onlamp.com/pub/a/onlamp/2003 ... ltext.html
something like this then?
edit: if none of this helps, sorry. I know a little about fulltext, but don't claim to be a master. =\
http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
http://www.onlamp.com/pub/a/onlamp/2003 ... ltext.html
something like this then?
Code: Select all
SELECT cars.brand_id, MATCH (cars.description) AGAINST ('$string') as cars_description, MATCH(car_brand.brand_name) AGAINST ('$string') as brand_name FROM cars INNER JOIN car_brand ON car_brand.brand_id = cars.brand_id WHERE MATCH (car_brand.brand_name, cars.description) AGAINST ('$string')edit: if none of this helps, sorry. I know a little about fulltext, but don't claim to be a master. =\
try:
EDIT:
Try this instead:
Code: Select all
SELECT cars.color, cars.description, cars_brands.brand_name
FROM cars
INNER JOIN car_brands
ON cars.brand_id = car_brands.brand_id
WHERE MATCH (car_brands.brand_name, cars.color, cars.description) AGAINST ('$string')Try this instead:
Code: Select all
SELECT `cars`.`color`, `cars`.`description`, `car_brands`.`brand_name`
FROM `cars`
INNER JOIN `car_brands` ON `cars`.`brand_id` = `car_brands`.`brand_id`
WHERE MATCH (
`cars`.`color`, `cars`.`description`
)
AGAINST (
'$string'
)
AND MATCH (
`car_brands`.`brand_name`
)
AGAINST (
'$string'
)