When it comes to creating a search-box, I do not want users to be limited to typing precise sentences in order to find the product they are looking for. If there is a product called "Kiwi Cubes" and I search for "Kiwi Slices", the Kiwi Cubes should still appear.
SELECT *
, ( (1.3 * (MATCH(wsp_product_title) AGAINST ('+kiwi+slices' IN BOOLEAN MODE)))
+ (0.6 * (MATCH(wsp_product_description) AGAINST ('+kiwi+slices'
IN BOOLEAN MODE)))) AS relevance
FROM webstore_products
LEFT JOIN webstore_product_size_variants ON wsp_product_id = wspsv_product_id
WHERE wsp_shop_id = 4
AND wspsv_visibility = 2
AND (MATCH(wsp_product_title,wsp_product_description) AGAINST
('+kiwi+slices' IN BOOLEAN MODE) )
GROUP BY wsp_product_id
ORDER BY relevance DESC
Here's a fiddle http://sqlfiddle.com/#!9/e13bcf/2
How can I adjust my query to include similar results even if nothing is found?
*N.B. The query successfully returns a value on my server... not sure why the fiddle isn't running.
My Answer:
"iPhone X" with the following details:
- Name: iPhone X
- Brand: Apple
- Price: $999
- Color: Space Gray
- Storage: 64GB
- Release Date: November 3, 2017
This information is stored in a database table called "products" with columns for name, brand, price, color, storage, and release date.
Rate this post
3 of 5 based on 4040 votesComments