Php Mysql使用多维数组显示和

0g0grzrc  于 2023-06-20  发布在  PHP
关注(0)|答案(2)|浏览(103)

以下数据的来源是来自仓库的csv文件。系统读取csv文件并保存在名为warehouse_feedback的表中。仓库将数量拆分并发送多个包裹。这就是为什么我在这里有相同的EAN多次。在这里,我被迫以从Warehouse接收的相同格式保存数据。因为我需要将相同的数据发送到其他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 code -->
<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></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>

这里有些我的代码是如何不考虑Order_number即使请求的数量是7如果你看到在ID - 4下它显示为6,在ID - 8下也是如此,总请求数量是26,它显示为18。
| ID| Package 编号|订单ID| EAN|申请数量|确认数量|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 1| 1| 3TAI91HB|电话:4046228076119|六|1|
| 2| 2| 3TAI91HB|电话:4046228076119|六|3|
| 3| 3| 3TAI91HB|电话:4046228076119|六|1|
| 4| 4| 6GF4GWRT|电话:4046228076119| 1| 1|
| | | | 电话:4046228076119传真:4046228076119|需求数量-6|确认数量-6|
| 5个|5个|31C3OD1H|电话:4046228076729| 5个|5个|
| | | | 电话:4046228076729传真:4046228076729|需求数量-5|确认数量-5|
| 六|二十三|1H32M34B|电话:4046228076123|十八岁|十二岁|
| 七个|二十四|1H32M34B|电话:4046228076123|十八岁|3|
| 八|二十五|1H32M34B|电话:4046228076456|八|5个|
| | | | 电话:4046228076456传真:4046228076456|需求数量-18|确认数量-20|
所以我想得到下面的结果。此处计数应基于order_idean
| ID| Package 编号|订单ID| EAN|申请数量|确认数量|
| - -----|- -----|- -----|- -----|- -----|- -----|
| 1| 1| 3TAI91HB|电话:4046228076119|六|1|
| 2| 2| 3TAI91HB|电话:4046228076119|六|3|
| 3| 3| 3TAI91HB|电话:4046228076119|六|1|
| | |3TAI91HB| 电话:4046228076119传真:4046228076119|需求数量-6|确认数量-5|
| 4| 4| 6GF4GWRT|电话:4046228076119|六|六|
| | | 6GF4GWRT**| 电话:4046228076119传真:4046228076119|需求数量-6|确认数量-6|
| 5个|5个|31C3OD1H|电话:4046228076729| 5个|5个|
| | | 31C3OD1H|电话:4046228076729传真:4046228076729|需求数量-5|确认数量-5|
| 六|二十三|1H32M34B|电话:4046228076123|十八岁|十二岁|
| 七个|二十四|1H32M34B|电话:4046228076123|十八岁|3|
| | |**1H32M34B|电话:4046228076123传真:4046228076123|需求数量-18|确认数量-15|
| 八|二十五|1H32M34B|电话:4046228076456|八|5个|
| | |**1H32M34B|电话:4046228076456传真:4046228076456|需求数量-8|确认数量-5|
Out put

zwghvu4y

zwghvu4y1#

你让自己的生活变得比需要的更困难。
我已经注解了代码,而不是写一个问题和更改的列表

#add an order by ean to your query
$stmt = $mysqliConn->prepare("SELECT id, hdr_id_fk, package_number, ean, 
                                     quantity, requested_qty, order_number 
                            from warehouse_feedback 
                            where hdr_id_fk =?  
                            ORDER BY ean");
$hdr_id = 31;
$stmt->bind_param("i", $hdr_id);
$stmt->execute();
$result = $stmt->get_result();
$results = $result->fetch_all(MYSQLI_ASSOC);

# no need for the pre-loop to reformat the result data
# process the results now as a long list watching for changes in the EAN code

<!-- table code -->
<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 
// initialise our counters and the current EAN code
$reqQty = 0;
$confQty = 0;
$curEan = null;
$lastRow = []; // so we can output the last row of an ean 

foreach ($results as $result) {

    if ( $EAN == $result['ean']) {
        # we are processing another of the same ean rows
        # so accumulate the totals for later
        $reqQty     += $result['quantity'];
        $confQty    += $result['order_number'];

        // save a couple of bits for the change of EAN situation
        $lastRow = [
                    'ean' => $result['ean'], 
                    'order_number' => $result['order_number'
                    ];
        
        // and then output a standard row        
?>
        <tr>
            <td><?php echo $result['id']; ?></td>
            <td><?php echo $result['package_number']; ?></td>
            <td><?php echo $result['order_number']; ?></td>
            <td><?php echo $result['ean']; ?></td>
            <td><?php echo $result['requested_qty']; ?></td>
            <td><?php echo $result['quantity']; ?></td>
        </tr>
<?php
    } else {
        // ean changed, so output totals for the previous ean 
        // that we have been accumulating
?>
        <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td><strong><?php echo $lastRow['order_number']; ?></strong></td>
        <td><strong><?php echo $lastRow['ean']; ?></strong></td>
        <td><?php echo "Req Qty - $reqQty"; ?></td>
        <td><?php echo "Confirmed Qty - $confQty"; ?></td>
    </tr>
<?php
        # so as we just output the totals row zeroise the counters
        $reqQty     = 0;
        $confQty    = 0;
        // and remember the new EAM
        $curEan     = $result['ean'];
    }
?>
        </tbody>
    </table>
g52tjvyc

g52tjvyc2#

Here is the answer to my question.

$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 = $reqQuantity = 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 '<b>Req Qty: ' . $reqQuantity.'</b>'; ?></td>
                    <td><?php echo '<b>Confrmd Qty: ' . $confirmedQuantity.'</b>'; ?></td>
                </tr>
            <?php } }?>
        </tbody>
    </table>

I don't know everyone down voted. Does this have format issues or not clear or did I just asked I want this / that without showing any effort?
Any have THANKS

相关问题