How to Add the Quantity of Duplicate Products entries. If the Product Quantity is Less than 8 return it

Question

In my API. I have a product table with attributes

product_id, category_id, item_id, size_id, brand_id, product_price, product_quantity, location_id, product_manufacture, product_expire, created_at

And I am returning the products information's response of which quantity is less than 8.

{
    "error": false,
    "message": "Products Found",
    "products": [
        {
            "productId": 6,
            "productCategory": "CAPSULE",
            "productName": "PYTHON",
            "productSize": "100 PILLS",
            "productBrand": "FHC",
            "productPrice": 401,
            "productQuantity": 5,
            "productLocation": "A1",
            "productManufacture": "2016-07",
            "productExpire": "2029-06"
        },
        {
            "productId": 5,
            "productCategory": "CAPSULE",
            "productName": "ANDROID",
            "productSize": "100 PILLS",
            "productBrand": "FHC",
            "productPrice": 401,
            "productQuantity": 5,
            "productLocation": "A1",
            "productManufacture": "2016-07",
            "productExpire": "2025-06"
        },
        {
            "productId": 4,
            "productCategory": "CAPSULE",
            "productName": "PYTHON",
            "productSize": "100 PILLS",
            "productBrand": "FHC",
            "productPrice": 401,
            "productQuantity": 6,
            "productLocation": "A1",
            "productManufacture": "2016-07",
            "productExpire": "2022-06"
        }
    ]
}

Here I am previewing data in client side.

This is code which is returning the product information.

function getNoticeProducts()
{
    $products = array();
    $productss = array();
    $productsss = array();
    $query = "
SELECT product_id
     , category_id
     , item_id
     , size_id
     , brand_id
     , product_price
     , product_quantity
     , location_id
     , product_manufacture
     , product_expire 
  FROM products 
 WHERE product_expire >= DATE_ADD(CURDATE(), INTERVAL 1 DAY) 
 ORDER 
    by product_expire DESC
";
    $stmt = $this->con->prepare($query);
    $stmt->execute();
    $stmt->bind_result($productId,$categoryId,$itemId,$sizeId,$brandId,$productPrice,$productQuantity,$locationId,$productManufacture,$productExpire);
    while ($stmt->fetch())
    {
        $product['productId']           = $productId;
        $product['categoryId']          = $categoryId;
        $product['itemId']              = $itemId;
        $product['sizeId']              = $sizeId;
        $product['brandId']             = $brandId;
        $product['productPrice']        = $productPrice;
        $product['productQuantity']     = $productQuantity;
        $product['locationId']          = $locationId;
        $product['productManufacture']  = $productManufacture;
        $product['productExpire']       = $productExpire;
        array_push($products, $product);
    }
    foreach ($products as  $product)
    {
        $salesQuantity = $this->getAllSalesQuantityOfProudctById($product['productId']);
        $sellerSalesQuantity = $this->getAllSellerSalesQuantityOfProudctById($product['productId']);
        if ($product['productQuantity']-$salesQuantity-$sellerSalesQuantity<8)
        {
            $pro['productId']               = $product['productId'];
            $pro['productCategory']         = $this->getCategoryById($product['categoryId']);
            $pro['productName']             = $this->getProductNameByItemId($product['itemId']);
            $pro['productSize']             = $this->getSizeById($product['sizeId']);
            $pro['productBrand']            = $this->getBrandById($product['brandId']);
            $pro['productPrice']            = $product['productPrice'];
            $pro['productQuantity']         = $product['productQuantity']-$this->getSellQuantityByProductId($pro['productId'])-$this->getAllSellerSalesQuantityOfProudctById($pro['productId']);
            $pro['productLocation']         = $this->getLocationById($product['locationId']);
            $pro['productManufacture']      = substr($product['productManufacture'], 0, 7);
            $pro['productExpire']           = substr($product['productExpire'], 0, 7);
            array_push($productss, $pro);
        }
    }
    return $productss;
}

But I have same product entries more than one times in database, the different between thus is product manufacture date.

Here I want to return the information after comparing like, if category_id,item_id,size_id and brand_id is same take the quantity of each product add it, and then only if the quantity is less than 8, return it.

How can I do this?

• Thanks.

Answer 1

The Problem has been solved by using SUM() and group by in query.

SELECT
    product_id,
    category_id,
    item_id,
    size_id,
    brand_id,
    product_price,
    SUM(product_quantity),
    location_id,
    product_manufacture,
    product_expire
FROM products
WHERE
    product_expire >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
GROUP BY (item_id),(brand_id),(category_id),(size_id)
ORDER by product_expire DESC