I am having the feature in my e-commerce application called store credits (advance payment) wherein the customer can redeem the store credits if he has in his account.
Everything is working fine till here.
What I want is that I want to display the data like this in the customer's My Account Details:
SC_OC S_OC SC_Amount SC_Alloted SC_Used SC_Balance
ORD-0001 -- 1000 1100 0 1100
-- ORD-0001 -- -- 300 800
SC_OC = Store_Credit_Order_Code
S_OC = Store_Order_Code
Whenever the customer purchase the store credits, if the SC_Alloted is not 0 then the store credit will update it by adding it. So it will be like this:
SC_OC S_OC SC_Amount SC_Alloted SC_Curent SC_Used SC_Balance
ORD-0001 -- 1000 1100 1100 0 1100
-- ORD-0001 -- -- 1100 300 800
ORD-0002 -- 2500 2800 3600 0 3600
The S_OC and SC_Used data is coming from the orders table and the rest of the details are coming from store_credits_orders table.
What I am getting is:
SC_OC S_OC SC_Amount SC_Alloted SC_Curent SC_Used SC_Balance
ORD-0001 ORD-0001 1000 1100 1100 0 1100
ORD-0001 ORD-0001 1000 1100 1100 300 800
ORD-0002 ORD-0001 2500 2800 3600 0 3600
Here's the PHP code that I have tried:
<?php
$table = '';
$queryToGetStoreCredit = "SELECT * FROM
store_credits_orders sco,
orders ord
WHERE
sco.SCO_CustEmailAdd = '".$_SESSION["Customer"]["email"]."'
AND
ord.CustEmailAdd = '".$_SESSION["Customer"]["email"]."'
AND
ord.CustEmailAdd = sco.SCO_CustEmailAdd";
$validate->Query($queryToGetStoreCredit);
if ($validate->NumRows() >= 1) {
while ($rows_sco = $validate->FetchAllDatas()) {
$i = 0;
$table .= '<tr>';
$table .= '<td>'.$rows_sco["SCO_OrderCode"].'</td>';
$table .= '<td>'.$rows_sco["OrderCode"].'</td>';
$table .= '<td>'.$rows_sco["SCO_OrderDate"].'</td>';
$table .= '<td>'.$rows_sco["SCO_Purchase_Amount"].'</td>';
$table .= '<td>'.$rows_sco["SCO_Credit_Alloted"].'</td>';
$table .= '<td>'.$rows_sco["AppliedCredits"].'</td>';
$table .= '<td>'.$rows_sco["SCO_Credit_Alloted"].'</td>';
$table .= '</tr>';
}
}
?>
How do I achieve that ?
Any help is highly appreciated.
Update 1:
After RST's answer, I get the following output:
SC_OC S_OC SC_Amount SC_Alloted SC_Curent SC_Used SC_Balance
ORD-0001 -- 1000 1100 1100 0 1100
ORD-0001 -- 1000 1100 1100 300 800
ORD-0002 -- 2500 2800 3600 0 3600
Update 2:
I have somehow tried to get the desired output. But I cannot get more than 1 result, meaning, if there are more than 1 orders in store_credits_orders, I get only the 1st result and not others. Here's the code:
<?php
$queryToGetStoreCredit = "SELECT * FROM store_credits_orders WHERE SCO_CustEmailAdd = '".$_SESSION["Customer"]["email"]."'";
$validate->Query($queryToGetStoreCredit);
if ($validate->NumRows() >= 1) {
while ($rows_sco = $validate->FetchAllDatas()) {
$used = $i = 0;
$table .= '<tr>';
$table .= '<td>'.$rows_sco["SCO_OrderCode"].'</td>';
$table .= '<td>--</td>';
$table .= '<td>'.$rows_sco["SCO_OrderDate"].'</td>';
$table .= '<td>'.$rows_sco["SCO_Purchase_Amount"].'</td>';
$table .= '<td>'.$rows_sco["SCO_Credit_Alloted"].'</td>';
$table .= '<td>'.$used.'</td>';
$table .= '<td>'.( $rows_sco["SCO_Credit_Alloted"] - $used ).'</td>';
$table .= '</tr>';
$queryToGetOrder = "SELECT * FROM orders WHERE CustEmailAdd = '".$rows_sco["SCO_CustEmailAdd"]."'";
$validate->Query($queryToGetOrder);
while ($row = $validate->FetchAllDatas()) {
$table .= '<tr>';
$table .= '<td>--</td>';
$table .= '<td>'.$row["OrderCode"].'</td>';
$table .= '<td>--</td>';
$table .= '<td>--</td>';
$table .= '<td>--</td>';
$table .= '<td>'.$row["AppliedCredits"].'</td>';
$table .= '<td>'.($rows_sco["SCO_Credit_Alloted"] - $row["AppliedCredits"]).'</td>';
$table .= '</tr>';
}
}
}
?>
Update 3:
After RST's comment, using print_r() on $rows_sco, I get the following array result:
Array
(
[SCO_Id] => 1
[SCO_OrderCode] => ORD-000001
[SCO_CustEmailAdd] => [email protected]
[SCO_Purchase_Amount] => 1
[SCO_Credit_Alloted] => 100
[SCO_OrderDate] => 2015-03-19 16:45:19
[SCO_OrderIP] => 115.97.1.132
[OrderId] => 1
[OrderCode] => ORD-000001
[CustEmailAdd] => [email protected]
[CustDelAddId] => 1
[ProdCode] => PK-0002-0004
[Quantity] => 1
[PaytMethod] => 2
[ShippingCharges] => 1
[TaxedAmount] => 1
[AppliedCredits] => 10
[PayableAmount] => 2
[OrderDate] => 2015-03-19 16:53:46
[OrderModified] => 0000-00-00 00:00:00
[OrderStatus] => In Process
[OrderIPAddress] => 115.97.1.132
)
Array
(
[SCO_Id] => 2
[SCO_OrderCode] => ORD-000002
[SCO_CustEmailAdd] => [email protected]
[SCO_Purchase_Amount] => 1
[SCO_Credit_Alloted] => 100
[SCO_OrderDate] => 2015-03-19 17:01:25
[SCO_OrderIP] => 115.97.1.132
[OrderId] => 1
[OrderCode] => ORD-000001
[CustEmailAdd] => [email protected]
[CustDelAddId] => 1
[ProdCode] => PK-0002-0004
[Quantity] => 1
[PaytMethod] => 2
[ShippingCharges] => 1
[TaxedAmount] => 1
[AppliedCredits] => 10
[PayableAmount] => 2
[OrderDate] => 2015-03-19 16:53:46
[OrderModified] => 0000-00-00 00:00:00
[OrderStatus] => In Process
[OrderIPAddress] => 115.97.1.132
)
Array
(
[SCO_Id] => 1
[SCO_OrderCode] => ORD-000001
[SCO_CustEmailAdd] => [email protected]
[SCO_Purchase_Amount] => 1
[SCO_Credit_Alloted] => 100
[SCO_OrderDate] => 2015-03-19 16:45:19
[SCO_OrderIP] => 115.97.1.132
[OrderId] => 2
[OrderCode] => ORD-000002
[CustEmailAdd] => [email protected]
[CustDelAddId] => 2
[ProdCode] => PK-0002-0004
[Quantity] => 1
[PaytMethod] => 2
[ShippingCharges] => 1
[TaxedAmount] => 1
[AppliedCredits] => 0
[PayableAmount] => 12
[OrderDate] => 2015-03-20 09:30:02
[OrderModified] => 0000-00-00 00:00:00
[OrderStatus] => In Process
[OrderIPAddress] => 115.97.1.132
)
Array
(
[SCO_Id] => 2
[SCO_OrderCode] => ORD-000002
[SCO_CustEmailAdd] => [email protected]
[SCO_Purchase_Amount] => 1
[SCO_Credit_Alloted] => 100
[SCO_OrderDate] => 2015-03-19 17:01:25
[SCO_OrderIP] => 115.97.1.132
[OrderId] => 2
[OrderCode] => ORD-000002
[CustEmailAdd] => [email protected]
[CustDelAddId] => 2
[ProdCode] => PK-0002-0004
[Quantity] => 1
[PaytMethod] => 2
[ShippingCharges] => 1
[TaxedAmount] => 1
[AppliedCredits] => 0
[PayableAmount] => 12
[OrderDate] => 2015-03-20 09:30:02
[OrderModified] => 0000-00-00 00:00:00
[OrderStatus] => In Process
[OrderIPAddress] => 115.97.1.132
)
P.S.: All the values are coming from the database.
See Question&Answers more detail:
os 
