Page 1 of 1
One FULLTEXT search, multiple tables
Posted: Sat Nov 26, 2005 5:35 pm
by 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? Any ideas? Thanks!
Posted: Sat Nov 26, 2005 6:01 pm
by Jenk
Try it?
Posted: Sat Nov 26, 2005 6:40 pm
by seodevhead
Yea I have tried it but have gotten errors saying unknown column.
Posted: Sat Nov 26, 2005 6:48 pm
by Jenk
post your statement(s)

Posted: Sat Nov 26, 2005 7:32 pm
by seodevhead
Thanks for the help... but I am not looking for any debugging help, but rather just a heads up as to if it is possible to perform a single FULLTEXT search over multiple columns in different tables.

Posted: Sat Nov 26, 2005 9:29 pm
by Zoxive
Mysql?
Code: Select all
SELECT * FROM ref_table,other_table
-NSF
Posted: Sat Nov 26, 2005 11:34 pm
by seodevhead
yes, mysql
Posted: Sun Nov 27, 2005 12:03 am
by Jenk
from googling, I don't think it is possible.
However, the reason I was asking for your statement(s) is so I can suggest possible ways to test if it does work.. but as you cba, neither can I.
Posted: Sun Nov 27, 2005 12:34 am
by josh
What's wrong with having an 'index' table dedicated for searches?
Posted: Sun Nov 27, 2005 1:50 am
by infolock
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.
Posted: Mon Nov 28, 2005 9:01 pm
by seodevhead
Here is my scenario...
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)
}
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!

Posted: Mon Nov 28, 2005 9:28 pm
by infolock
is this what you are looking for?
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_id
or, if you want to include the text searches...
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_id WHERE car_brands.brand_name LIKE %$some_brand_var% AND cars.description LIKE %$some_description_var% AND cars.color = '$some_color_var'
or if you actually are only allowing them to specify, submit like % to = '$var'
Posted: Mon Nov 28, 2005 9:36 pm
by seodevhead
Well see.. I know I can do a standard LIKE search over multiple tables, but what I really want to learn how to do is do it using FULLTEXT search. This is what I am after

Posted: Mon Nov 28, 2005 9:40 pm
by infolock
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?
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. =\
Posted: Tue Nov 29, 2005 4:54 am
by Jenk
try:
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')
EDIT:
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'
)