osCommerce Get Cheapest Product in Category

Here’s a function that you can use to find and get the cheapest product in any category. The function looks at the base price (product_price) and special price (specials_new_products_price) and chooses the lesser value; I added it to includes/functions/general.php, but you can add it anywhere it makes sense to do so.

 
////
// Get Cheapest Product in Category
// 
  function tep_get_cheapest_prod_in_cat($cat_id=0) {
 
      global $languages_id;
      $res=false;
 
      $categories = array();
      $categories_query_addition = "p2c.categories_id = '" .$cat_id . "'";
      tep_get_sub_categories($categories, $cat_id);
      foreach ($categories AS $key => $category ) {
          $categories_query_addition .= " OR p2c.categories_id = '" . (int)$category . "'";
      }
 
      $product_query="SELECT
                                p.products_id,
                                p.products_tax_class_id,
                                pd.products_name,                                
                                s.status as specstat,
                                MIN((CASE
                                        WHEN
                                            s.specials_new_products_price>0 AND s.specials_new_products_price<=p.products_price
                                        THEN
                                            s.specials_new_products_price
                                        ELSE
                                            p.products_price
                                        END
                                )) as min_price
                           FROM (
                                        " . TABLE_PRODUCTS . " p
                                    LEFT JOIN
                                        " . TABLE_SPECIALS . " s
                                    ON
                                        p.products_id = s.products_id,
                                        " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c,
                                        " . TABLE_CATEGORIES . " c
                                   )
                           LEFT JOIN
                                    " . TABLE_PRODUCTS_DESCRIPTION . " pd
                           ON
                                    p.products_id = pd.products_id
                           AND
                                    pd.language_id = '" . (int)$languages_id . "'
                           WHERE
                                    p.products_id = p2c.products_id
                           AND
                                    p2c.categories_id = c.categories_id
                           AND
                                    (".$categories_query_addition.")
                           AND
                                    p.products_status =1";
 
       $product_res = tep_db_query($product_query);
       if(tep_db_num_rows($product_res)){
           $res=tep_db_fetch_array($product_res);
       }
       return $res;
 
  }

Leave a Reply

Your email address will not be published. Required fields are marked *