Cannot add array values, what's wrong?

Question

I am making an eCommerce web application having commission as one of the feature and I am working on it.

The orders table:

CREATE TABLE `orders` (
 `OrderId` int(11) NOT NULL AUTO_INCREMENT,
 `OrderCode` varchar(20) NOT NULL,
 `CustEmailAdd` varchar(80) NOT NULL,
 `CustDelAddId` varchar(255) NOT NULL,
 `ProdCode` varchar(255) NOT NULL,
 `Quantity` varchar(100) NOT NULL,
 `PaytMethod` varchar(255) NOT NULL,
 `ShippingCharges` float NOT NULL,
 `TaxedAmount` float NOT NULL,
 `AppliedCredits` int(11) NOT NULL,
 `PayableAmount` varchar(255) NOT NULL,
 `OrderDate` datetime NOT NULL,
 `OrderModified` datetime NOT NULL,
 `OrderStatus` varchar(255) NOT NULL,
 `OrderIPAddress` varchar(20) NOT NULL,
 PRIMARY KEY (`OrderId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

The commission table:

CREATE TABLE `commission` (
 `CommId` int(11) NOT NULL AUTO_INCREMENT,
 `OrderCode` varchar(20) NOT NULL,
 `ProdCode` varchar(255) NOT NULL,
 `ModCode` varchar(255) NOT NULL,
 `AffiCode` varchar(255) NOT NULL,
 `BenCode` varchar(255) NOT NULL,
 `ModCommAmount` varchar(255) NOT NULL,
 `AffiCommAmount` varchar(255) NOT NULL,
 `BenCommAmount` varchar(255) NOT NULL,
 PRIMARY KEY (`CommId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

The affiliate_commission_payable table

CREATE TABLE `affiliate_commission_payable` (
 `ACP_Id` int(11) NOT NULL AUTO_INCREMENT,
 `CommId` int(11) NOT NULL,
 `ACP_PaymentStatus` varchar(255) NOT NULL,
 `ACP_PaymentDate` varchar(255) NOT NULL,
 `ACP_PaymentDetails` text NOT NULL,
 PRIMARY KEY (`ACP_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

I have an array like so:

// For Order 000001    
    Array
    (
        [0] => ORD-000001
    )
    Array
    (
        [Paid] => 26.25
        [Due] => 42.75
    )

// For Order 000002
    Array
    (
        [0] => ORD-000002
    )
    Array
    (
        [Cancelled] => 33.75
        [Due] => 13.5
    )
// For Order 000003
    Array
    (
        [0] => ORD-000003
    )
    Array
    (
        [Paid] => 13.50
        [Paid] => 14.25
    )

What I am trying to do is showing the order commission summary to the user. It is in the table format like so:

OrderCode    Paid Comm    Cancelled Comm    Due Comm    View Details

ORD-000001   addAllPaid   addAllCancelled   addAllDue       View

ORD-000002   addAllPaid   addAllCancelled   addAllDue       View

ORD-000003   addAllPaid   addAllCancelled   addAllDue       View

If there is no value in the array for commission, replace it with 0, else add them and display it based on the order code. How do I achieve this ?

The code that I have tried so far:->

<?php
$qu = "SELECT
           c.*, o.*, acp.*, .*, a.*
       FROM
           customers c, orders o, affiliate_commission_payable acp, commission com, affiliates a
       WHERE
           a.AffiCode = '".$affiCode."'
               AND
           c.AffiCode = '".$affiCode."'
               AND
           c.CustEmailAdd = o.CustEmailAdd
               AND
           acp.CommId = com.commId
               AND
           com.OrderCode = o.OrderCode
               AND
           com.AffiCode = c.AffiCode";
$validate->Query($qu);
if ($validate->NumRows() >= 1) {
    while ($rows = $validate->FetchAllDatas()) {
        $commId = $rows["CommId"];
        $orderCode = $rows["OrderCode"];
        $orderDate = $rows["OrderDate"];
        $arrCommissionStatus = explode(', ', $rows["ACP_PaymentStatus"]);
        $prdCodes = explode(', ', $rows["ProdCode"] );
        $arrAffiCommAmount = explode(', ', $rows["AffiCommAmount"]);

        $ord = explode(', ', $rows["OrderCode"]);

        $prdCodeAndCommStatus = array_combine($prdCodes, $arrCommissionStatus);
        $arrCommAndStatus = array_combine($arrCommissionStatus, $arrAffiCommAmount);

        $table .= "<tr>";
        $table .= "<td>".$orderCode."</td>";
        $table .= "<td>".$orderDate."</td>";
        $table .= "<td>".count($prdCodes)."</td>";

        foreach ($arrCommAndStatus as $key => $value) {
            if ($value == 'Due') {
                $totDueComm += $key;
            } else {
                $totDueComm = 0;
            }
        }
        foreach ($arrCommAndStatus as $key => $value) {
            if ($value == 'Cancelled') {
                $totCancellComm += $key;
            } else {
                $totCancellComm = 0;
            }
        }
        foreach ($arrCommAndStatus as $key => $value) {
            if ($value == 'Paid') {
                $totPaidComm += $key;
            } else {
                $totPaidComm = 0;
            }
        }

        $table .= "<td>".number_format($totPaidComm, 2)."</td>";
        $table .= "<td>".number_format($totCancellComm, 2)."</td>";
        $table .= "<td>".number_format($totDueComm, 2)."</td>";
        $table .= "<td>".number_format(array_sum($arrAffiCommAmount), 2)."</td>";
        $table .= "<td><a href='//www.example.com/nbs/Affiliates/Commission.php?ord=".$orderCode."&id=".$commId."'>View</a></td>";
        $table .= "</tr>";

    }   
}

UPDATE 1:

I have somehow tried to get it done.. Here's the code that I had to change inside the while loop above:

$strQ = "SELECT com.*, acp.* FROM commission com, affiliate_commission_payable acp WHERE com.AffiCode = '".$affiCode."' AND com.OrderCode = '".$orderCode."' AND com.CommId = acp.CommId";
$validate->Query($strQ);
if ($validate->NumRows() >= 1) {
    while ($rows_strQ = $validate->FetchAllDatas()) {
        $comAmt = explode(', ', $rows_strQ["AffiCommAmount"]);
        $coStat = explode(', ', $rows_strQ["ACP_PaymentStatus"]);
        $ts = array_combine( $comAmt, $coStat);
        foreach ($ts as $key => $value) {
            if (in_array($value, $sta) && $value == 'Due') {
                $totDueComm += $key;
                $totalDueCommission += $totDueComm;
            }                       
        }
    }
}

But the update has just done it for 1st order and not all the orders. Where have I gone wrong ? Kindly help me out. Thanks.

UPDATE 2:

I have somehow managed it to get it done.. But there is a problem that the amount is getting added to the previous order amount and then the amount is displayed.. For example, Say I have Rs. 300 in Order 1 and Rs. 200 in Order 2.. So for Order 1, it will display Rs. 300 and for Order 2, the amount shown is Rs. 500. I want it to show Rs. 200 only and not Rs 500. How do I solve this ? Please help me.. Thanks once again.

Here's the code that I have used so far:

$sta = array('Due', 'Cancelled', 'Paid');

$qu = "SELECT c.*, o.*, acp.*, com.*, a.* FROM customers c, orders o, affiliate_commission_payable acp, commission com, affiliates a WHERE a.AffiCode = '".$affiCode."' AND c.AffiCode = '".$affiCode."' AND c.CustEmailAdd = o.CustEmailAdd AND acp.CommId = com.commId AND com.OrderCode = o.OrderCode AND com.AffiCode = c.AffiCode GROUP BY o.OrderCode";
$validate->Query($qu);
if ($validate->NumRows() >= 1) {
    while ($rows = $validate->FetchAllDatas()) {
        $commId = $rows["CommId"];
        $orderCode = $rows["OrderCode"];
        $orderDate = $rows["OrderDate"];
        $arrCommissionStatus = explode(', ', $rows["ACP_PaymentStatus"]);
        $prdCodes = explode(', ', $rows["ProdCode"] );
        $arrAffiCommAmount = explode(', ', $rows["AffiCommAmount"]);

        $prdCodeAndCommStatus = array_combine($prdCodes, $arrCommissionStatus);
        $arrCommAndStatus = array_combine($arrAffiCommAmount, $arrCommissionStatus);

        $table .= "<tr>";
        $table .= "<td>".$orderCode."</td>";
        $table .= "<td>".$orderDate."</td>";
        $table .= "<td>".count($prdCodes)."</td>";

        foreach ($rows as $key => $value) {
            if ($key === "AffiCommAmount") {
                $comAmt = explode(', ', $rows["AffiCommAmount"]);
            }
            if ($key === "ACP_PaymentStatus") {
                $coStat = explode(', ', $rows["ACP_PaymentStatus"]);
            }
        }
        $ts = array_combine( $comAmt, $coStat);

        foreach ($ts as $k => $v) {
            if (in_array($v, $sta) && $v == 'Due') {
                $totDueComm += $k;
                $totalDueCommission = $totDueComm;
            }
        }



        $table .= "<td>".number_format($totPaidComm, 2)."</td>";
        $table .= "<td>".number_format($totCancellComm, 2)."</td>";
        $table .= "<td>".number_format($totDueComm, 2)."</td>";
        $table .= "<td>".number_format(array_sum($arrAffiCommAmount), 2)."</td>";
        $table .= "<td><a href='//www.example.com/nbs/Affiliates/Commission.php?ord=".$orderCode."&id=".$commId."'>View</a></td>";
        $table .= "</tr>";
    }

}

?>

If I understant well your question, you are willing to sum all commissions for each order and display the total in an html table, am I right ? If yes, can you post your database structure please ? — kitensei
– kitensei, Commented Mar 5, 2015 at 8:58
Yeah.. You are right.. But the total should be order wise only.. — Saiyan Prince
– Saiyan Prince, Commented Mar 5, 2015 at 9:01
Excuse my lack of english wording, but "order wise" means order related ? (commissions grouped by order ?), I could still use the database arch :) — kitensei
– kitensei, Commented Mar 5, 2015 at 9:09
2 pics on 3 doesn't load, please just copy/paste a SHOW CREATE TABLE schema.table for the concerned tables — kitensei
– kitensei, Commented Mar 5, 2015 at 9:17

kitensei · Accepted Answer · 2015-03-05 11:40:01Z

0

Based on chat, here the solution: http://ideone.com/tXoWDk

I'm not sure of understanding all fields in your database structure, but if, as you said, want to sum commissions for each order in your database, here a sample of how to do that:

select 
    o.code, sum(c.amount) as total
from orders o
inner join com c on c.orderId = o.id
group by o.code

The point is that doing that in PHP is useless as MySQL can handle it as well without any further processing, so if I simplify the structure like this:

mysql> select * from orders;
+----+------------+
| id | code       |
+----+------------+
|  1 | ORDER-0001 |
|  2 | ORDER-0002 |
+----+------------+

mysql> select * from com;
+----+--------+---------+-----------+
| id | amount | orderId | status    |
+----+--------+---------+-----------+
|  1 |     15 |       1 | DUE       |
|  2 |     12 |       1 | DUE       |
|  3 |      2 |       2 | CANCELLED |
|  4 |     23 |       2 | PAID      |
|  5 |      3 |       1 | CANCELLED |
|  6 |      5 |       1 | PAID      |
|  7 |     12 |       2 | CANCELLED |
+----+--------+---------+-----------+


mysql> select
    -> o.code, c.status, sum(c.amount) as total
    -> from orders o
    -> inner join com c on c.orderId = o.id
    -> group by c.status, o.code
    -> order by o.code, c.status, c.amount;
+------------+-----------+-------+
| code       | status    | total |
+------------+-----------+-------+
| ORDER-0001 | CANCELLED |     3 |
| ORDER-0001 | DUE       |    27 |
| ORDER-0001 | PAID      |     5 |
| ORDER-0002 | CANCELLED |    14 |
| ORDER-0002 | PAID      |    23 |
+------------+-----------+-------+

and to sum the total, just apply the following to your result:

$ordersTotalCommissions = [];

// fetch your data
foreach ($result as $line) {

    // initialize the 'order' group by putting 0 as a commission
    if (!isset($ordersTotalCommissions[$line['code']])) {
        $ordersTotalCommissions[$line['code']] = 0;
    }

    // sum each commission
    $ordersTotalCommissions[$line['code']] += $line['total'];
}

print_r($ordersTotalCommissions);

Tell me if that helps you

edited Mar 5, 2015 at 11:40

answered Mar 5, 2015 at 10:00

kitensei

2,5402 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saiyan Prince Over a year ago

Yes.. It did helped me out.. But as I said, I want to bifurcate it on the basis of paid, due and cancelled.. And add those and display it.. Add those and show it again as the total commission on the order. What you did is the total commission.. I want the bifurcation.. Still.. I appreciate your help.. Thanks for that..

Saiyan Prince Over a year ago

I know this Sir.. But I have stored the amount as comma separated and then I have exploded it and trying to add it.. Can you help me with that ?

Collectives™ on Stack Overflow

Cannot add array values, what's wrong?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related