有什么不对的,而循环? [英] Is something wrong in the while loop?
问题描述
我有一个store_credits_orders表
I have a store_credits_orders table
和订单表。
这是我想要的输出与此类似,但根据店铺信用秩序的日期和时间
和商店订购日期和时间
:
The output that I want is similar to this but based on Store Credit Order Date and Time
and Store Order Date and Time
:
在code,我到目前为止已经试过:
The code that I have tried so far:
<?php
$table = '';
$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>--</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>';
$validate2 = new Validation();
$queryToGetOrder = "SELECT * FROM orders WHERE CustEmailAdd = '".$rows_sco["SCO_CustEmailAdd"]."'";
$validate2->Query($queryToGetOrder);
while ($row = $validate2->FetchAllDatas()) {
$table .= '<tr>';
$table .= '<td>--</td>';
$table .= '<td>'.$row["OrderCode"].'</td>';
$table .= '<td>--</td>';
$table .= '<td>'.$row["OrderDate"].'</td>';
$table .= '<td>--</td>';
$table .= '<td>--</td>';
$table .= '<td>'.$row["AppliedCredits"].'</td>';
$table .= '<td>'.($rows_sco["SCO_Credit_Alloted"] - $row["AppliedCredits"]).'</td>';
$table .= '</tr>';
}
}
}
?>
我想实现的是,每当有购买store_credits,信息将被插入在 store_credits_orders
表。现在,当同一个用户来了,下单和赎回store_credits(大于等于,他在他的帐户具有更低),将有比在插入订单表中的其他数据库没有更新。
What I want to achieve is, whenever there is a purchase of store_credits, the information will get inserted in the store_credits_orders
table. Now when the same user comes, places an order and redeems the store_credits (less than equal to, he has in his account), there will be no update in the database other than insertion in the orders table.
但是,当用户登录时,他应该能够看到,当他已经购买了store_credits和/或当他赎回store_credits。所有这些活动应该由哪个事件发生第一次下令,不论购买或赎回。
But when the user logs in, he should be able to see when he has purchased the store_credits and/or when he has redeemed the store_credits. All these events should be ordered by whichever event happens first, irrespective of purchase or redeem.
推荐答案
我跟@ user3514160,我得到了清晰,他想的有什么。数据基本上被正确插入。只有他想的是按照正确的方式来显示数据。
I talked to @user3514160 and I got clarity what he want's to have. Basically data gets inserted correctly. Only thing he wants is to show data in correct way.
您的查询应该是这样的。
(SELECT
SCO_Id,
SCO_OrderCode,
SCO_CustEmailAdd,
SCO_Purchase_Amount,
SCO_Credit_Alloted,
SCO_OrderDate,
SCO_OrderIP,
null AS OrderId,
null AS OrderCode,
null AS CustEmailAdd,
null AS AppliedCredits,
null AS OrderDate,
'store_credits_orders' AS tableName
FROM `store_credits_orders` AS credits_orders WHERE `SCO_CustEmailAdd` = '".$_SESSION["Customer"]["email"]."')
UNION
(SELECT
OrderId,
OrderCode,
CustEmailAdd,
AppliedCredits,
OrderDate
null AS SCO_Id,
null AS SCO_OrderCode,
null AS SCO_CustEmailAdd,
null AS SCO_Purchase_Amount,
null AS SCO_Credit_Alloted,
OrderDate AS SCO_OrderDate,
null AS SCO_OrderIP,
'store_orders' AS tableName
FROM `store_orders` AS orders WHERE `SCO_CustEmailAdd` = '".$_SESSION["Customer"]["email"]."')
ORDER BY `SCO_OrderDate` ASC
这是需要得到一个结果的所有行。我们做到这一点与 MySQL的UNION 。
This is needed to get all rows in one result. We accomplish this with MySQL UNION.
UNION需要两个表有有相同的名字列相同。这就是为什么我们添加空列与 NULL作为COLUMNNAME
。
UNION needs both tables to have same amount of columns with same names. This is why we add empty columns with null AS columnName
.
和 PHP 与查询
<?php
$table = '<table>';
// Query to get all rows from both tables
$queryToGetStoreCredit =
"(SELECT
SCO_Id,
SCO_OrderCode,
SCO_CustEmailAdd,
SCO_Purchase_Amount,
SCO_Credit_Alloted,
SCO_OrderDate,
SCO_OrderIP,
null AS OrderId,
null AS OrderCode,
null AS CustEmailAdd,
null AS AppliedCredits,
null AS OrderDate,
'store_credits_orders' AS tableName
FROM `store_credits_orders` AS credits_orders WHERE `SCO_CustEmailAdd` = '".$_SESSION["Customer"]["email"]."')
UNION
(SELECT
OrderId,
OrderCode,
CustEmailAdd,
AppliedCredits,
OrderDate
null AS SCO_Id,
null AS SCO_OrderCode,
null AS SCO_CustEmailAdd,
null AS SCO_Purchase_Amount,
null AS SCO_Credit_Alloted,
OrderDate AS SCO_OrderDate,
null AS SCO_OrderIP,
'store_orders' AS tableName
FROM `store_orders` AS orders WHERE `SCO_CustEmailAdd` = '".$_SESSION["Customer"]["email"]."')
ORDER BY `SCO_OrderDate` ASC";
$validate->Query($queryToGetStoreCredit);
if ($validate->NumRows() >= 1) {
// Starting balance. This could be some other number, for example if viewing some certain period of orders etc.
$balance = 0;
while ($row = $validate->FetchAllDatas()) {
// Add to balance
if($row['tableName'] == 'store_credits_orders' && (int)$row['SCO_Credit_Alloted'] > 0){
$balance += (int)$row['SCO_Credit_Alloted']
}
// Remove from balance
else if($row['tableName'] == 'store_orders' && (int)$row['AppliedCredits'] > 0){
$balance -= (int)$row['AppliedCredits'];
}
$table .= '<tr>';
$table .= '<td>'.$row["SCO_OrderCode"].'</td>';
$table .= '<td>'.$row["OrderCode"].'</td>';
$table .= '<td>'.(($row['tableName'] == 'store_credits_orders') ? $row["SCO_OrderDate"] : '').'</td>';
$table .= '<td>'.$row["OrderDate"].'</td>';
$table .= '<td>'.$row["SCO_Purchase_Amount"].'</td>';
$table .= '<td>'.$row["SCO_Credit_Alloted"].'</td>';
$table .= '<td>'.$row["AppliedCredits"].'</td>';
$table .= '<td>'.$balance.'</td>';
$table .= '</tr>';
}
}
$table .= '</table>';
echo $table;
?>
让我知道,如果有一些问题。
Let me know, if there is some problems.
这篇关于有什么不对的,而循环?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!