Logical error to display MySQL table data

Question

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.

(This question seems to have been abandoned and re-asked here). — halfer
– halfer, Commented Mar 26, 2015 at 12:59

RST · Accepted Answer · 2015-03-19 07:48:36Z

1

Before displaying the rowdata check the value of SC_Used.

If it is 0 set the value of S_OC to '--'

If it is not 0 set the value of SC_OC, SC_Amount, SC_Alloted to '--'

then display the row

in code:

while ($rows_sco = $validate->FetchAllDatas()) {
    $i = 0;
    if ( 0 == $rows_sco["AppliedCredits"]) {
      $rows_sco["OrderCode"] = '--';
    } else {
      $rows_sco["SCO_OrderCode"] = '--';
      $rows_sco["SCO_Purchase_Amount"] = '--';
      $rows_sco["SCO_Credit_Alloted"] = '--';
    }
    $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>';

}

you may need to replace 0 with '0'

edited Mar 19, 2015 at 7:48

answered Mar 19, 2015 at 7:40

RST

3,9252 gold badges24 silver badges33 bronze badges

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

4 Comments

RST Over a year ago

based on your information, my code is correct. Did you check whether the $rows_sco["AppliedCredits"] is 0 or '0'?

RST Over a year ago

Like I said you need to check the value $rows_sco["AppliedCredits"] right now the if-loop is running the == 0 part on every row. Do a var_dump() or print_r()on $row_sco

RST Over a year ago

Still no reason to adjust my code. Can you check whether the AppliedCredits value is stored as text or int in the database? And can you remove the ecommerce tag from your question? The issue is not related to ecommerce, it is just a loop condition issue.

halfer Over a year ago

@RST: this question has been asked in a duplicate - thought you might wish to know. See the comment under the question. (Have removed the ecommerce tag).

Collectives™ on Stack Overflow

Logical error to display MySQL table data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related