I’ve been implementing the WordPress e-Commerce plug-in on another site, it’s not a bad freebie but if you’re selling anything more than a handful of items then you’ll need to let your users search your products.  To add this functionality, along with a few other features, you need to purchase the $50 Gold Cart add-in.

I duly purchased and installed the update only to find that the “search” only looked at product titles, meaning that all the description text, additional description text and meta tags were being ignored.   Looking around it appears I wasn’t the only person who needed this functionality and I was able to get hold of some code to change in gold_shopping_cart.php. I still didn’t get the results I was expecting so after checking out the Database table structure I went from

?View Code PHP
$category_list = $wpdb->get_col("SELECT `id` FROM `".WPSC_TABLE_PRODUCT_CATEGORIES."` WHERE `name` LIKE '".$search_string_title."'");

to

?View Code PHP
$category_list = $wpdb->get_col("SELECT id FROM `".WPSC_TABLE_PRODUCT_LIST."` WHERE name LIKE '".$search_string_title."' OR description LIKE '".$search_string_title."' OR additional_description LIKE '".$search_string_title."'");

Notice the change in the table being searched. The original code I found still looked in the WPSC_TABLE_PRODUCT_CATEGORIES table but this does not contain the additional_description field, an oversight on the original modders part.

Yet I still wasn’t getting the expected results. A quick count of the $category_list array showed I was getting results but they were getting filtered out further on in the function by this little check:

?View Code PHP
if($category_list != null) {
				$category_assoc_list = $wpdb->get_col("SELECT DISTINCT `product_id` FROM `".WPSC_TABLE_ITEM_CATEGORY_ASSOC."` WHERE `category_id` IN ('".implode("', '", $category_list)."')");
				$category_sql = "OR `".WPSC_TABLE_PRODUCT_LIST."`.`id` IN ('".implode("', '", $category_assoc_list)."')";
      }

Remember that I’m now searching products directly and the above code is looking to match the products unique ID against a category ID, clearly this will fail on 99% of searches. A quick change to the search criteria is all that was needed to return valid results:

?View Code PHP
if($category_list != null) {
				$category_assoc_list = $wpdb->get_col("SELECT DISTINCT `product_id` FROM `".WPSC_TABLE_ITEM_CATEGORY_ASSOC."` WHERE `product_id` IN ('".implode("', '", $category_list)."')");
				$category_sql = "OR `".WPSC_TABLE_PRODUCT_LIST."`.`id` IN ('".implode("', '", $category_assoc_list)."')";
      }

With that completed I looked at adding a separate search interface and came across WP e-Commerce Search Widget, a handy bit of code but unfortunately it doesn’t appear to be compatible with the latest version of WP e-Commerce so I had to quickly update

?View Code PHP
<form method='GET' name='product_search' id="productsearchform" action="<?php bloginfo('home');  ?>/">

to

?View Code PHP
<form method='GET' name='product_search' id="productsearchform" action="<?php bloginfo('home');  ?>/products-page/">

WP e-Commerce is a lot of work, especially considering you have to pay $50 just to get basic functionality and then hack in the desired features. Lets hope the checkout refresh bug doesn’t hit again….

Tags: , , ,