Php Mysql display sum using multi dimentional Array

user3408779
June 20, 2023
167 views
0 votes
2 Answers

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->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>

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

Tags: mysql php

Answers

Chosen as BEST ANSWER

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

(Edit)

You have made life a little more difficult for yourself than it needs to be.

I have commented the code rather than writing a list of issues and changes

#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>

Please signup or login to give your own answer.

Click here to cancel reply.