Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Post an Article
Welcome To Ask or Share your Answers For Others

Categories

php - Logical error to display MySQL table data

0 votes
354 views
in Technique[技术] by (71.8m points)

php - Logical error to display MySQL table data

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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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] => harishmdesai@yahoo.com
    [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

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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'


与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界，一个免费开放的程序员编程交流平台！开放，进步，分享！让技术改变生活，让极客改变未来！ Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

Just Browsing Browsing

1.4m articles

1.4m replys

5 comments

57.0k users

Most popular tags

Snow Theme by Q2A Market
Powered by Question2Answer
...