Re: [PHP-DB] Searching shopping cart

Ron Piggott wrote:
> Someone referred me to:
> 
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html 
> 
> Does this look like I am on the right track?
> 
> Ron
> 
> SELECT MATCH(shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST ('$keyword') as Relevance
> FROM ( shopping_cart_category INNER JOIN shopping_cart_product ON
> shopping_cart_category.reference =
> shopping_cart_product.category_reference ) INNER JOIN
> shopping_cart_product_image ON
> shopping_cart_product_image.product_reference =
> shopping_cart_product.reference INNER JOIN shopping_cart_inventory ON
> shopping_cart_inventory.product_reference =
> shopping_cart_product.reference WHERE MATCH
> (shopping_cart_product.product_description,
> shopping_cart_product.product_name) AGAINST('$keyword' IN BOOLEAN MODE)
> HAVING Relevance > 0.2 ORDER
> BY Relevance DESC

My first suggestion :) Use table aliases.

Instead of:

shopping_cart_category inner join shopping_cart_product

do

shopping_cart_category cat inner join shopping_cart_product prod

and so on (give them all a short name), so then your query becomes a
little easier to read:

select match(prod.product_description, prod.product_name) ....

http://dev.mysql.com/doc/refman/5.0/en/join.html


Secondly - do you need to do all of those joins to do a keyword search?
If you're searching a particular category, then include that category
limitation. If you're not, don't include it - the database will do less
work. Same for images etc.

It looks like you're on the right track though.

-- 
Postgresql & php tutorials
http://www.designmagick.com/