时间戳

首页 > 解决方案 > 从加入php sql中的多个表计算小计作为总计

php - 从加入php sql中的多个表计算小计作为总计

问题描述

我有 3 张这样的桌子

订单表

orders `order_id`,`order_date`

Orders_items 表

orders_items `order_id`,`product_id`,`quantity`,`parent`

产品表

products `product_id`,`product_price`

我的期望

I want to make grand total in last row and dynamically, I have built my own query and work to show data from multiple tables above, but cannot display grand total as I mentioned above.

这是我的查询

<?php

    $query = $db->prepare("SELECT * FROM orders WHERE month(order_date)='".$_POST['bulan']."' AND YEAR(order_date)='".$_POST['tahun']."'");

?>
<table class="table table-hover table-bordered display" id="report" width="100%">
    <thead>
        <tr>
            <th>Tanggal</th>
            <th>Customer</th>
            <th>Quantity</th>
            <th>Harga</th>
            <th>Total Harga</th>
        </tr>
    </thead>
    <tbody>

    <?php
        $query->execute(); 
        $row = $query->rowcount(); 

        if($row > 0){ 
            while($data = $query->fetch(PDO::FETCH_ASSOC)){ 
                $oID = $data['order_id'];
                $item = $db->prepare("SELECT * FROM orders_items WHERE order_id = '$oID' AND parent=3");
                $item->execute();
                while($i = $item->fetch(PDO::FETCH_ASSOC)){
                    $pID = $i['product_id'];
                    $product = $db->prepare("SELECT * FROM products WHERE product_id = '$pID'");
                    $product->execute();
                    while($p = $product->fetch(PDO::FETCH_ASSOC)){
                    //fech data
                    echo    "<tr>
                                <td>{$data['order_date']}</td>
                                <td>{$data['order_name']}</td>
                                <td>{$i['quantity']}</td>
                                <td>Rp. ".number_format($p['product_price'])."</td>
                                <td>Rp. ".number_format($p['product_price']*$i['quantity'])."</td>
                            </tr>";
                    }

                }
                    $total = 0;
                    $oID = $data['order_id'];
                    $rinci = $db->prepare("SELECT orders_items.order_id, SUM(orders_items.quantity * products.product_price ) AS grand_total 
                    FROM orders_items JOIN products ON products.product_id=orders_items.product_id WHERE order_id = '$oID' 
                    GROUP BY order_id");
                    $rinci->execute();
                    foreach($rinci as $r){
                    $total += $r['grand_total'];
        ?>
                    <tr class="bg-primary text-white">
                        <td class="text-center"><strong>Total</strong></td>
                        <td></td>
                        <td></td>
                        <td></td>
                        <td><strong>Rp. <?=number_format($total);?></strong></td>
                    </tr>

我的查询根据 order_id 将几行显示为总计，如果我更改查询并按另一列分组，总计将消失。这是我根据上面的查询得出的结果

这是我的表格，总共有几行

如何使小计仅显示在行尾并汇总所有总价*数量？

对不起我的英语不好。

非常感谢给我答案并解决我的问题的人。

标签： phpmysql

解决方案

可能这会起作用

   while($data = $query->fetch(PDO::FETCH_ASSOC)){
        $oID = $data['order_id'];
        $item = $db->prepare("SELECT order_items.*, products.*, (order_items.quantity*products.product_price) as total FROM order_items LEFT JOIN products ON products.product_id = order_items.product_id WHERE order_id='$oID' AND parent=3");
        $item->execute();
        while($i = $item->fetch(PDO::FETCH_ASSOC)){
            ?>
            <tr>
                <td><?php echo $data['order_date'];?></td>
                <td><?php echo $data['order_name'];?></td>
                <td><?php echo $i['quantity'];?></td>
                <td>Rp. <?php echo number_format($i['product_price']);?></td>
                <td>Rp. <?php echo number_format($i['product_price']*$i['quantity']);?></td>
            </tr>
            <?php
        }
    }

推荐阅读