创建统计页面。Php

Question

我试图在我的网站中创建一个统计页面，以显示属于贸易商的产品、每种产品的销售量以及已售出的特定产品的总价。

目前，我的代码创建了一个while循环，但是当它遍历数据库时，它会多次显示项目(而不是每个产品只显示一次)，并且每个产品的总销量没有设置为某个产品，而是显示了每个产品的总销量。

以下是我的select语句：

      $querytest = "SELECT * FROM PRODUCT
                    INNER JOIN TRADER
                    ON PRODUCT.Stall_ID=TRADER.Stall_ID
                    INNER JOIN ORDER_ITEMS
                    ON PRODUCT.Product_ID=ORDER_ITEMS.Product_ID
                    WHERE TRADER.Username='". $_SESSION['Username'] ."'";

      $testresult = oci_parse($connection, $querytest);
      oci_execute($testresult)

这是我的时间循环。我希望它只输出一次数据库中的产品。但它却把它们输出了无数次。

    while($row = oci_fetch_assoc($testresult)) {
      $DName = $row ['NAME'];
      $DProdesc = $row ['PRODUCT_DESC'];
      $DIMAGEID = $row['IMAGEID'];
      $DPrice = $row['PRICE'];

      $price = $row['PRICE'];

      $prodord = $row['PRODUCTQUANTITY'];

      $totalprodord = $totalprodord + $prodord;

      $totalprodprice = $totalprodord * $Dprice;

      $quantity = $row['QUANTITY'];

      $subtotal = ($price * $quantity);

      $total = $total + $subtotal;

      $stock = $stock + $quantity;





            echo  '<td> <img src="' . $DIMAGEID.  '" height="50" width="50"/></td>';
            echo  '<td>' .$DName. '</td>';
            echo  '<td>' .$DProdesc. '</td>';
            echo '<td>' .$totalprodord. '</td>';
            echo '<td>&pound; ' .$DPrice.  '</td>';
                  echo '<td>&pound; ' . $totalprodprice . '</td>';'</tr>';
    }


      ob_flush();
      ?>

我的数据库表：

CREATE TABLE TRADER(
    Trader_ID number (5) NOT NULL,
    Trader_Name varchar2 (20),
    FirstName varchar2 (10) NOT NULL,
    LastName varchar2 (10) NOT NULL,
    DOB date NOT NULL,
    Username varchar2 (15) NOT NULL,
    Password varchar2 (32) NOT NULL,
    Phone varchar2 (10),
    Email varchar2 (30) NOT NULL,
    Stall_ID number (5) references STALL(Stall_ID),
    Primary Key (Trader_ID));

CREATE TABLE PRODUCT(
    Product_ID number (5) NOT NULL,
    Name varchar2 (30) NOT NULL,
    Product_Desc varchar2 (50),
    ImageID varchar2 (35),
    Price number(5,2) NOT NULL,
    Quantity number (10) NOT NULL,
    Stall_ID number (5) references STALL(Stall_ID),
    Product_Type_ID number (5) references PRODUCT_TYPE(Product_Type_ID),
    Primary Key (Product_ID));

CREATE TABLE ORDER_ITEMS(
    Order_Items_ID number (5) NOT NULL,
    TotalPrice number (5,2) NOT NULL,
    ProductQuantity number (5) NOT NULL,
    Day_ID number (5) references COLLECTION_DAY(Day_ID),
    Time_ID number (5) references COLLECTION_TIME(Time_ID),
    Customer_ID number (5) references CUSTOMER(Customer_ID),
    Product_ID number (5) references PRODUCT(Product_ID),
    Order_ID number (5) references CUSTOMER_ORDER(Order_ID),
    Primary Key (Order_Items_ID));

有人能帮我把数据正确地显示出来吗？

谢谢

Answer 1

您需要在查询中总结您的数字，或者您必须在代码中总结数字并跳过相同的产品。你这两样都没做。

尝试将查询替换为如下所示：

SELECT prod.product_id, prod.name, 
prod.quality-sum(orders.quality) AS Stock_Left, 
AVG(orders.price) AS Sold_price, 
SUM(orders.price * orders.quality) AS Sold_Amount, 
Sum(orders.quality) AS Total_Sold 
FROM product prod INNER JOIN Order_Items orders
ON prod.prod_id = orders.prod_id 
INNER JOIN trader 
ON prod.stall_id = trader.stall_id
WHERE trader.Username='". $_SESSION['Username'] ."'"

你可以在循环中去掉大部分的计算。