adding fulltext query to existing query

Question

I am struggling to work out the syntax for joining a normal multi table query with single table fulltext. I can't get my head around the syntax

This is the existing syntax which works fine:

SELECT * FROM products,categories,sub_categories 
WHERE products.approved = 1 
AND products.main_category = categories.id 
AND products.sub_category = sub_categories.id

My efforts to add fulltext are not cutting it. This is the query i am trying to use fo the search:

MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
AGAINST ('keyword') AS score FROM products 
WHERE MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword')

This is what i came up with, but for (probably obvious) reasons unknown to me, its not working.

SELECT * FROM products,categories,sub_categories 
WHERE products.approved = 1 
AND products.main_category = categories.id 
AND products.sub_category = sub_categories.id
AND MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
AGAINST ('keyword') AS score FROM products 
WHERE MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword')

Any help would be much appreciated!

Answer 1

Bare minimum, you need to move the "score" into the SELECT clause, and combine the two WHERE clases with AND.

SELECT *, 
  MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
  AGAINST ('keyword') AS score
FROM products,categories,sub_categories 
WHERE products.approved = 1 
  AND products.main_category = categories.id 
  AND products.sub_category = sub_categories.id
  AND MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword')

I would also highly recommend rewriting the entire query with ANSI join syntax to make it more readable:

SELECT *, 
  MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
  AGAINST ('keyword') AS score
FROM products
  INNER JOIN categories on products.main_category = categories.id
  INNER JOIN sub_categories on products.sub_category = sub_categories.id
WHERE products.approved = 1 
  AND MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword')

Answer 2

thanks heaps for that. im pretty good with php and other languages, but flakey with mysql syntax. To be honest, as i look at your code, things are starting to fall into place! I can kinda see how you came to that result. Ill give it a whirl, but it looks good.

Answer 3

great - got it working! thanks so much

Answer 4

how would I add an 'order by product_name' to that? I tried this, but the results are not order correctly

SELECT * FROM products 
INNER JOIN product_categories on products.main_category = product_categories.id 
INNER JOIN product_sub_categories on products.sub_category = product_sub_categories.id 
WHERE products.approved = 1 AND products.main_category = 8 
ORDER BY products.product_name DESC LIMIT 0,25 

This however does work when a search is added

SELECT * , 
MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,product_details) 
AGAINST ('keyword') AS score FROM my_products 
INNER JOIN my_product_categories on my_products.main_category = my_product_categories.id 
INNER JOIN my_product_sub_categories on my_products.sub_category = my_product_sub_categories.id 
WHERE my_products.approved = 1 AND 
MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,product_details) 
AGAINST('bath') ORDER BY my_products.product_name ASC LIMIT 0,25