Multiple MySQL Queries and fatching MIN price from a table

Question

TABLE PRODUCTS_CATEGORIES

product_id    category_id    link_type    position
2             22             M            0
3             22             M            0
4             22             M            0
5             22             M            0
6             1              M            0
7             1              M            0
8             1              M            0
9             1              M            0
10            1              M            0
11            1              M            0


TABLE PRODUCT_PRICES

product_id  price  lower_limit  usergroup_id
2           39.99  1            0
3           69.99  1            0
4           99.99  1            0
5           124.99 1            0
6           169.99 1            0
7           199.99 1            0
8           249.99 1            0
9           24.99  1            0
10          29.99  1            0
11          34.99  1            0

I want to be able to grab the lowest products price from the category - the function i have currently made is:

function fn_get_category_min_price($category_id)
{
    if (!empty($category_id)) 
    {
        $product_min_price = db_get_field("SELECT product_id FROM ?:products_categories WHERE category_id = ?i", $category_id);
        $category_min_price = db_get_field("SELECT MIN(price) FROM ?:product_prices WHERE product_id = ?i", $product_min_price);
        if (!empty($category_min_price)) 
        {
            return $category_min_price;
        } 
        else 
        {
            return "";
        }
    }
    return false;
}

but it is not 100% working, although it grabs the price from the right category_id - it does not appear to be grabbing the lowest all the time.... anyone have any ideas or a better way of writing those mysql queries??

Answer 1

As far as I understand, your code takes first product from specified category and then just returns it's price. Sometimes it's minimum, sometimes not.

This can be done with single SQL query:

select min(price) from product_prices p, product_categories c where p.product_id=c.product_id and c.category_id = $category_id

Answer 2

You can do this with an single SQL query by joining the two tables (Natural join):

Select Min(`prize`)
  from product_categories natural join product_prices
  where category_id = ?i