One FULLTEXT search, multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

One FULLTEXT search, multiple tables

Post 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!
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Try it?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

Yea I have tried it but have gotten errors saying unknown column.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

post your statement(s) :)
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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. :)
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

Mysql?

Code: Select all

SELECT * FROM ref_table,other_table
-NSF
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

yes, mysql
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

What's wrong with having an 'index' table dedicated for searches?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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! :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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'
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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 :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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. =\
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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'
)
Post Reply