Php Mysql 使用多维数组显示总和

huangapple go评论73阅读模式
英文:

Php Mysql display sum using multi dimentional Array

问题

以下是您提供的代码的翻译部分:

数据来源是一个来自仓库的CSV文件。系统读取CSV文件并保存在名为"warehouse_feedback"的表中。仓库将数量拆分并发送多个包裹。这就是为什么我在这里有相同的EAN多次的原因。在这里,我被迫以与我从仓库收到的相同格式保存数据。因为我需要将这些相同的数据发送到其他ERP系统。另一件事是相同的EAN将出现在不同的订单ID下。

我能够在一定程度上显示信息。以下是获取下面输出的我的代码。

$ stmt = $mysqliConn->prepare("SELECT id, hdr_id_fk, package_number, ean, 
                                 quantity, requested_qty, order_number 
                from warehouse_feedback 
                where hdr_id_fk =? ");
$ hdr_id = 31;
$ stmt->bind_param("i", $ hdr_id);
$ stmt->execute();
$ result = $ stmt->get_result();
$ results = $ result->fetch_all(MYSQLI_ASSOC);
$ finalResults = [];
foreach($results as $result){
    $ finalResults[$result['ean']][] = $ result;
}

<!-- 表格代码 -->
<table border="1">
        <thead>
            <tr>
                <th>ID</th>
                <th>Pack_num</th>
                <th>Order Id</th>
                <th>EAN</th>
                <th>ReqQty</th>
                <th>Confrmd Qty</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($finalResults as $finalResult) {
                $confirmedQuantity = 0
            ?>

                <?php foreach ($finalResult as $finalRes) {
                    $confirmedQuantity += $finalRes['quantity'];
                ?>

                    <tr>
                        <td><?php echo $finalRes['id']; ?></td>
                        <td><?php echo $finalRes['package_number']; ?></td>
                        <td><?php echo $finalRes['order_number']; ?></td>
                        <td><?php echo $finalRes['ean']; ?></td>
                        <td><?php echo $finalRes['requested_qty']; ?></td>
                        <td><?php echo $finalRes['quantity']; ?></td>
                    </tr>
                <?php } ?>
                <tr>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td><?php echo $finalResult[0]['ean']; ?></td>
                    <td><?php echo 'Req Qty -' . $finalResult[0]['requested_qty'] ?></td>
                    <td><?php echo 'Confirmed Qty -' . $confirmedQuantity; ?></td>
                </tr>
            <?php } ?>
        </tbody>
    </table>

请注意,这仅包括您提供的代码的翻译部分,不包括任何其他内容。如果您需要更多帮助或有其他问题,请随时提出。

英文:

The source of the below data is a csv file that comes from a warehouse. System reads the csv file and saves in the table called warehouse_feedback. Warehouse splits the quantity and sends in multiple packages. That is the reason here I am having the same EANs multiple times. Here I am forced to save data in the same format as I received from Warehouse. Because I need to send this same data to other ERP system. Another thing is Same EAN will appear under different order ids.

I am able to display information up to some extent. Here is my code to get the below out put.

$stmt = $mysqliConn-&gt;prepare(&quot;SELECT id, hdr_id_fk, package_number, ean, 
quantity, requested_qty, order_number 
from warehouse_feedback 
where hdr_id_fk =?  &quot;);
$hdr_id = 31;
$stmt-&gt;bind_param(&quot;i&quot;, $hdr_id);
$stmt-&gt;execute();
$result = $stmt-&gt;get_result();
$results = $result-&gt;fetch_all(MYSQLI_ASSOC);
$finalResults = [];
foreach($results as $result){
$finalResults[$result[&#39;ean&#39;]][] = $result;
}
&lt;!-- table code --&gt;
&lt;table border=&quot;1&quot;&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;ID&lt;/th&gt;
&lt;th&gt;Pack_num&lt;/th&gt;
&lt;th&gt;Order Id&lt;/th&gt;
&lt;th&gt;EAN&lt;/th&gt;
&lt;th&gt;ReqQty&lt;/th&gt;
&lt;th&gt;Confrmd Qty&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;?php foreach ($finalResults as $finalResult) {
$confirmedQuantity = 0
?&gt;
&lt;?php foreach ($finalResult as $finalRes) {
$confirmedQuantity += $finalRes[&#39;quantity&#39;];
?&gt;
&lt;tr&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;id&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;package_number&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;order_number&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;ean&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;requested_qty&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;quantity&#39;]; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } ?&gt;
&lt;tr&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalResult[0][&#39;ean&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo &#39;Req Qty -&#39; . $finalResult[0][&#39;requested_qty&#39;] ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo &#39;Confirmed Qty -&#39; . $confirmedQuantity; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } ?&gt;
&lt;/tbody&gt;
&lt;/table&gt;

Here some how my code is not considering Order_number Even the requested quantity is 7 If you see Under ID - 4 it is showing as 6 and same in Under ID - 8 , total requested quantity is 26, and it is showing as 18.

ID Pack_num Order Id EAN ReqQty Confrmd Qty
1 1 3TAI91HB 4046228076119 6 1
2 2 3TAI91HB 4046228076119 6 3
3 3 3TAI91HB 4046228076119 6 1
4 4 6GF4GWRT 4046228076119 1 1
4046228076119 Req Qty - 6 Confrmd Qty - 6
5 5 31C3OD1H 4046228076729 5 5
4046228076729 Req Qty - 5 Confrmd Qty - 5
6 23 1H32M34B 4046228076123 18 12
7 24 1H32M34B 4046228076123 18 3
8 25 1H32M34B 4046228076456 8 5
4046228076456 Req Qty - 18 Confrmd Qty - 20

So I would like to have the below result.
Here count should be based on order_id and ean

ID Pack_num Order Id EAN ReqQty Confrmd Qty
1 1 3TAI91HB 4046228076119 6 1
2 2 3TAI91HB 4046228076119 6 3
3 3 3TAI91HB 4046228076119 6 1
3TAI91HB 4046228076119 Req Qty - 6 Confrmd Qty - 5
4 4 6GF4GWRT 4046228076119 6 6
6GF4GWRT 4046228076119 Req Qty - 6 Confrmd Qty - 6
5 5 31C3OD1H 4046228076729 5 5
31C3OD1H 4046228076729 Req Qty - 5 Confrmd Qty - 5
6 23 1H32M34B 4046228076123 18 12
7 24 1H32M34B 4046228076123 18 3
1H32M34B 4046228076123 Req Qty - 18 Confrmd Qty - 15
8 25 1H32M34B 4046228076456 8 5
1H32M34B 4046228076456 Req Qty - 8 Confrmd Qty - 5

Out put
Php Mysql 使用多维数组显示总和

Php Mysql 使用多维数组显示总和

答案1

得分: 1

以下是您要翻译的代码部分的内容:

$stmt = $mysqliConn->prepare("SELECT id,hdr_id_fk,package_number,ean,quantity,requested_qty,order_number,sscc_code from desadv_message_lines where hdr_id_fk =? ");
$hdr_id = 31;
$stmt->bind_param("i", $hdr_id);
$stmt->execute();
$result = $stmt->get_result();
$results = $result->fetch_all(MYSQLI_ASSOC);
$finalResults = [];
foreach($results as $result){
    $finalResults[$result['ean']][$result['order_number']][] = $result;
}
?>

<!-- table code -->
<table border="1">
    <thead>
        <tr>
            <th>ID</th>
            <th>Pack_num</th>
            <th>SSCC</th>
            <th>Order Id</th>
            <th>EAN</th>
            <th>ReqQty</th>
            <th>Confrmd Qty</th>
        </tr>
    </thead>
    <tbody>
        <?php 
        foreach ($finalResults as $finalResultItem) {                
            foreach ($finalResultItem as $finalResult) {         
                $confirmedQuantity = 0;
                foreach ($finalResult as $finalRes) {
                    $confirmedQuantity += $finalRes['quantity'];
                    $reqQuantity = $finalRes['requested_qty'];
                    ?>
                    <tr>
                        <td><?php echo $finalRes['id']; ?></td>
                        <td><?php echo $finalRes['package_number']; ?></td>
                        <td><?php echo $finalRes['sscc_code']; ?></td>
                        <td><?php echo $finalRes['order_number']; ?></td>
                        <td><?php echo $finalRes['ean']; ?></td>
                        <td><?php echo $finalRes['requested_qty']; ?></td>
                        <td><?php echo $finalRes['quantity']; ?></td>
                    </tr>
                    <?php
                } 
                ?>
                <tr>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td><?php echo $finalResult[0]['order_number']; ?></td>
                    <td><?php echo $finalResult[0]['ean']; ?></td>
                    <td><?php echo 'Req Qty - ' . $finalResult[0]['requested_qty']; ?></td>
                    <td><?php echo '<b>Confrmd Qty: ' . $confirmedQuantity.'</b>'; ?></td>
                </tr>
                <?php
            }
        }?>
    </tbody>
</table>
英文:

Here is the answer to my question.

&lt;?php
$stmt = $mysqliConn-&gt;prepare(&quot;SELECT id,hdr_id_fk,package_number,ean,quantity,requested_qty,order_number,sscc_code from desadv_message_lines where hdr_id_fk =? &quot;);
$hdr_id = 31;
$stmt-&gt;bind_param(&quot;i&quot;, $hdr_id);
$stmt-&gt;execute();
$result = $stmt-&gt;get_result();
$results = $result-&gt;fetch_all(MYSQLI_ASSOC);
$finalResults = [];
foreach($results as $result){
$finalResults[$result[&#39;ean&#39;]][$result[&#39;order_number&#39;]][] = $result;
}
?&gt;
&lt;!-- table code --&gt;
&lt;table border=&quot;1&quot;&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;ID&lt;/th&gt;
&lt;th&gt;Pack_num&lt;/th&gt;
&lt;th&gt;SSCC&lt;/th&gt;
&lt;th&gt;Order Id&lt;/th&gt;
&lt;th&gt;EAN&lt;/th&gt;
&lt;th&gt;ReqQty&lt;/th&gt;
&lt;th&gt;Confrmd Qty&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;?php 
foreach ($finalResults as $finalResultItem) {                
foreach ($finalResultItem as $finalResult) {         
$confirmedQuantity = 0;
foreach ($finalResult as $finalRes) {
$confirmedQuantity += $finalRes[&#39;quantity&#39;];
$reqQuantity = $finalRes[&#39;requested_qty&#39;];
?&gt;
&lt;tr&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;id&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;package_number&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;sscc_code&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;order_number&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;ean&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;requested_qty&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalRes[&#39;quantity&#39;]; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } ?&gt;
&lt;tr&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalResult[0][&#39;order_number&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo $finalResult[0][&#39;ean&#39;]; ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo &#39;Req Qty -&#39; . $finalResult[0][&#39;requested_qty&#39;] ?&gt;&lt;/td&gt;
&lt;td&gt;&lt;?php echo &#39;&lt;b&gt;Confrmd Qty: &#39; . $confirmedQuantity.&#39;&lt;/b&gt;&#39;; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } }?&gt;
&lt;/tbody&gt;
&lt;/table&gt;

huangapple
  • 本文由 发表于 2023年6月19日 23:39:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76508150.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定