adding fulltext query to existing query
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 s开发者_运维百科core 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!
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')
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.
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
精彩评论