my queries all work great which is good but it is pulling the min and max prices for all products and I only want to pull the data for products that have a status of "a"
I have the condition within the queries, but don't think it's correct. The table cscart_product_prices does not have status only cscart_products so need to some how add the condition in to just pass back status a for what prices are pulled back if that makes sense?
$proMaxPrice = db_get_row("select max(price) as mianprice FROM cscart_product_prices as cpp LEFT JOIN cscart_products_categories as cpc on cpc.product_id=cpp.product_id LEFT JOIN cscart_products AS cp ON cpc.product_id = cp.product_id where cpc.category_id ='".$key."' AND cp.status='A' ");
$proMinPrice = db_get_row("select min(price) as mianprice FROM cscart_product_prices as cpp LEFT JOIN cscart_products_categories as cpc on cpc.product_id=cpp.product_id LEFT JOIN cscart_products AS cp ON cpc.product_id = cp.product_id where cpc.category_id ='".$key."' AND cp.status='A' ");
It more or less brings back min and max price for products within the categories, but regardless of what status the products have, "A" is active, "D" is disabled and "H" is hidden... its getting price for all and I just need for products that are "A" active.
Without table definitions and some sample data is not easy to figure out what the expected query is.
However, have you thought about using inner joins instead of left joins? Also, I would rewrite the query like this:
select max(price) as mianprice
FROM cscart_product_prices as cpp
LEFT JOIN cscart_products_categories as cpc on cpc.product_id=cpp.product_id AND
cpc.category_id ='".$key."'
LEFT JOIN cscart_products AS cp ON cpc.product_id = cp.product_id AND cp.status='A'