Using sql function min() in php loop

Question

I have to output some products from table 'products', along with the lowest price from the table 'product_licenses', which is the only column I need from that table in this query.

However, when I try to use the sql function MIN(), my loop only runs through the code once and gets the first result and then it stops, so I am a bit lost here.

This is the query using min() :

$mysql->query("
    SELECT pd.*, min(lc.price) AS price
    FROM `products` AS pd, product_licenses AS lc
    WHERE pd.`status` = '1' AND lc.product_id = pd.id
    ORDER BY pd.`id` ASC
    $limitQuery
");

I'm using this function to get the products, but this, unfortunately, fetches the highest price:

public function getAllProducts($start = 0, $limit = 0, $order = '`datetime` ASC') {
    global $mysql;


    $limitQuery = '';
    if ($limit != 0) {
        $limitQuery = " LIMIT $start,$limit ";
    }
    **// Not working if I use min() on lc.price**
    $mysql->query("
        SELECT pd.*, lc.price
        FROM `products` AS pd, product_licenses AS lc
        WHERE pd.`status` = '1' AND lc.product_id = pd.id
        ORDER BY pd.`id` ASC
        $limitQuery
    ");

    if ($mysql->num_rows() == 0) {
        return false;
    }

    $this->usersWhere = '';
    $return = array();
    while ($d = $mysql->fetch_array()) {
        $categories = explode(',', $d['category_id']);
        unset($d['category_id']);
        foreach ($categories as $c) {
            $c = trim($c);
            if ($c != '') {
                $d['category_id'][$c] = $c;
            }
        }

        $return[$d['id']] = $d;
    }

    $this->foundRows = $mysql->getFoundRows();

return $return;
}

Answer 1

Add GROUP BY in your query. your current query returns only one result since your are using aggregate function (MIN) but not grouping it.

SELECT  pd.col1, 
        pd.col2, min(lc.price) AS PRICE
FROM   `products` AS pd
             INNER JOIN product_licenses AS lc
                  ON lc.product_id = pd.id
WHERE    pd.`status` = '1' 
GROUP BY pd.col1, pd.col2, pd.col3
ORDER BY pd.`id` ASC
$limitQuery

PS: post the structure of your database with records. It will the community understands your question clearly :)

Answer 2

You have no group by clause in your query, so the query is returning the first row only.

SELECT pd.col1, pd.col2, pd.col3,  min(lc.price) AS price
FROM `products` AS pd, product_licenses AS lc
WHERE pd.`status` = '1' AND lc.product_id = pd.id
group by pd.col1, pd.col2, pd.col3
ORDER BY pd.`id` ASC
$limitQuery