Welcome Guest, Not a member yet? Register   Sign In
Query using FullText Search across multiple tables (that are all linked by relations)
#1

[eluser]drakeonfire[/eluser]
Hello,

Basically I'm having this issue for a search, I need it so it can search a few tables, that are all linked to this central one. For example:

Table: suppliers
Columns: id, name, town, city, county

Table: stores
Columns: id, supplier_id, opt_town, opt_city, opt_county

Table: categories
Columns: id, name

Table: categories_stores (JOIN TABLE)
Columns: id, category_id, store_id

Now I'm essentially using stores as the main table in this, as each store has one supplier (one supplier may have many stores, hence the optional town and city, and county locations), and each store may have many categories (e.g. DIY, Gardening, Grocery) and a category may have many stores.

I'm trying to do a search that allows the user to search something like:
"pineapple this_town" or something along those lines, so in my search I want to use Full Text Search to try and accomplish this.

The idea is it will check the following colums:
suppliers.name, suppliers.town, suppliers.city, suppliers.county, stores.opt_town, stores.opt_city, stores.opt_county, categories.name

So far I have something along these lines:
Code:
"SELECT MATCH(`stores`.`opt_town`,`stores`.`opt_city`,`stores`.`opt_county`)
AGAINST ('".$search."') AS `relevance`, `stores`.*, `suppliers`.*
FROM `stores`
LEFT JOIN `suppliers` ON `stores`.`supplier_id` = `suppliers`.`id`
WHERE MATCH (`stores`.`opt_town`,`stores`.`opt_city`,`stores`.`opt_county`) AGAINST('".$search."' IN BOOLEAN MODE)
HAVING `relevance` > 0.2 ORDER BY `relevance` DESC"

The problems I have:
I can't seem to add other table columns to the MATCH part (e.g. suppliers.name), when I do I get this error: "Incorrect arguments to MATCH"
And when I try and join the categories JOIN table on, it returns each result multiple times (the amount depending on how many categories the store belongs to).

Any ideas?

Thank you!
#2

[eluser]drakeonfire[/eluser]
Still stuck unfortunately Sad




Theme © iAndrew 2016 - Forum software by © MyBB