从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将几行显示为总计，如果我更改查询并按另一列分组，则总计将消失。这是我基于上面查询的结果

这是我的表，共有几行

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

对不起我的英语不好。

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