Salemaker SQL help needed

[djs]

In my wife's quilting store, she has products where the same product may be in multiple categories (linked). I have salemaker installed, which seems to work correctly, except it only checks a product for the first category that is listed in the database, not all of the categories that product is in.

 

If a piece of fabric is listed in 2 categories, say "Reds" and "Marbles", and the reds category has a 20% sale, when you view that product in Marbles, it should still be on sale. It turns out that you have a 50-50 chance whether it will be on sale in either category. If the product was first added to reds, then it will be on sale in both. If it was first added to marbles, then it won't be on sale in either category.

 

I need help in figuring out how to check ALL categories a product might belong to, not just the first one found.

 

The code that appears to run the test, is a modification done to general.php, and I believe it is this section:

 

    $product_to_categories_query = tep_db_query("select categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $product_id . "'");
   $product_to_categories = tep_db_fetch_array($product_to_categories_query);
   $category = $product_to_categories['categories_id'];

   $sale_query = tep_db_query("select sale_specials_condition, sale_deduction_value, sale_deduction_type from " . TABLE_SALEMAKER_SALES . " where sale_categories_all like '%," . $category . ",%' and sale_status = '1' and (sale_date_start <= now() or sale_date_start = '0000-00-00') and (sale_date_end >= now() or sale_date_end = '0000-00-00') and (sale_pricerange_from <= '" . $product_price . "' or sale_pricerange_from = '0') and (sale_pricerange_to >= '" . $product_price . "' or sale_pricerange_to = '0')");
   if (tep_db_num_rows($sale_query)) {
     $sale = tep_db_fetch_array($sale_query);
   } else {
  return $special_price;
   }

 

In general, we would probably never have more than one category on sale at a time, so the second part of the challenge isn't as important, but if someone is really up to the challange.......

 

If a product belongs to 2 (or more) categories, AND both categories have a sale at different discounts, it would be helpful to look at both and determine the larger discount, and return that as the special price.

 

Any help is appreciated!

 

Dan

[djs]

This is one of those problems I'm just not able to get my mind around and I'm having a hard time figuring this out. Does anyone know where similar code might exist in osCommerce that I can use as an example to make this work?

 

Dan

[millyramsey]

I am experiencing the same thing, and would really love to know if you found a solution to this.

Thanks!

[♥bruyndoncx]

you will have to put the section from $product_to_categories in a while loop to fetch each possible category, run the category sale maker queries and compare the result to the previous run(s) to find the biggest category discount, or eventually get individual sale price if there is any.

 

Makes sense ?

[millyramsey]

bruyndoncx, hhmmm. Makes some sense, but not quite enough for me.