+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query using FullText Search across multiple tables (that are all linked by relations) (/showthread.php?tid=49558)
Query using FullText Search across multiple tables (that are all linked by relations) - El Forum - 02-24-2012
[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!
Query using FullText Search across multiple tables (that are all linked by relations) - El Forum - 02-26-2012
[eluser]drakeonfire[/eluser]
Still stuck unfortunately
