[EDIT 3]
我试过下面的代码,但只从主表中得到一行。
<?php
include ("config.php");
$results = $mysqli->query
("SELECT transaction_id FROM orders_list WHERE customer_name = 'Al Kasih' ORDER BY id");
if ($results) {
while($obj = $results->fetch_object()) {
echo '<div>';
echo '<ul>';
echo '<li>'.$obj->transaction_id.'</li>';
$thisthat=$obj->transaction_id;
$results = $mysqli->query
("SELECT transaction_id, items, quantity, one_product_price FROM orders_history
WHERE transaction_id = '$thisthat'");
if ($results) {
while($obj = $results->fetch_object()) {
echo '<ul>';
echo '<li>'.$obj->items.'</li>';
echo '<li>'.$obj->quantity.'</li>';
echo '<li>'.$obj->one_product_price.'</li>';
echo '</ul>';
}
}
echo '</div>';
echo '</ul>';
}
}
?>
在order_list
的表格中,它将显示所有客户的所有订单历史记录。
-----------------------------------------------------------------------
id_cart products quantity invoices status
-----------------------------------------------------------------------
0001 this 2 $20 delivered
0001 that 1 $20 pending
0001 those 2 $20 approved
0002 this 2 $20 delivered
0002 that 1 $20 pending
0002 those 2 $20 approved
0003 this 2 $20 delivered
0003 that 1 $20 pending
0003 those 2 $20 approved
0004 this 2 $20 delivered
0004 that 1 $20 pending
0004 those 2 $20 approved
-----------------------------------------------------------------------
而在member_cart表中,它将只显示客户的名称及其唯一的id_cart。
-------------------------------------------
id_cart customer payment
-------------------------------------------
0001 Klaudia creditcard
0002 Klaudia paypal
0003 MyFather Transfer
0004 MyMother Transfer
-------------------------------------------
在名为Klaudia的客户的页面账户中,我想显示基于id_cart 的所有订单历史记录
-----------------------------------------------------------------------
id_cart products quantity invoices status
-----------------------------------------------------------------------
this 2 $20 delivered
0001 that 1 $20 pending
those 2 $20 approved
-----------------------------------------------------------------------
this 2 $20 delivered
0002 that 1 $20 pending
those 2 $20 approved
-----------------------------------------------------------------------
如何在查询中进行/选择,以便克劳迪亚页面账户中的结果返回如下。我还在学习如何加入谈判桌。
注意:cart_id将是唯一的,因为它实际上是将发送到数据库表的日期/月/年/小时/秒/毫秒的隐藏输入。
////////[更新]/////////''/////
Fisrt我要选择表格
SELECT order_list.id_cart,
order_list.products,
order_list.quantity,
order_list.invoices,
order_list.status,
FROM order_list, member_carts
WHERE order_list.id_cart = member_carts.id_cart
AND member_carts.customer = 'Klaudia';
然后检索查询:
$id_cart = $row['$id_cart'];
$products = $row['$products'];
$quantity = $row['$quantity'];
$invoices = $row['$invoices'];
$status = $row['$status'];
现在复杂的部分是如何循环
<table width="780" border="1">
<tr>
<td width="134">id_cart</td>
<td width="173">products</td>
<td width="155">quantity</td>
<td width="135">invoices</td>
<td width="149">status</td>
</tr>
<tr>
<td rowspan="3"> ?php echo $id_cart ? </td>
<td> ?php echo $products ? </td>
<td> ?php echo $quantity ? </td>
<td> ?php echo $invoices ? </td>
<td> ?php echo $status ? </td>
</tr>
<tr>
<td> looping of products </td>
<td> looping of quantity </td>
<td> looping of invoices </td>
<td> looping of status </td>
</tr>
<tr>
<td> looping of products </td>
<td> looping of quantity </td>
<td> looping of invoices </td>
<td> looping of status </td>
</tr>
SQL查询:
SELECT order_list.id_cart,
order_list.products,
order_list.quantity,
order_list.invoices,
order_list.status,
FROM order_list, member_carts
WHERE order_list.id_cart = member_carts.id_cart
AND member_carts.customer = 'Klaudia';
此查询将解决您的问题。。。
查询获取数据:
SELECT *
FROM order_list ol
JOIN member_carts mc
ON ol.id_cart = mc.id_cart
WHERE customer = 'Klaudia'
ORDER BY ol.id_cart
//更新
对于计算购物车中的行,您可以使用以下代码:
$cart_items_count = array();
for($result as $row) {
if(!isset($cart_items_count[$row['cart_id']]) {
$cart_items_count[$row['cart_id']] = 0;
}
$cart_items_count[$row['cart_id']]++;
}
对于显示数据:
<table width="780" border="1">
<tr>
<td width="134">id_cart</td>
<td width="173">products</td>
<td width="155">quantity</td>
<td width="135">invoices</td>
<td width="149">status</td>
</tr>
<?php $current_cart_id = null; ?>
<?php foreach($results as $row): ?>
<?php
$id_cart = $row['$id_cart'];
$products = $row['$products'];
$quantity = $row['$quantity'];
$invoices = $row['$invoices'];
$status = $row['$status'];
?>
<tr>
<?php if($id_cart != $current_cart_id): ?>
<td rowspan="<?php echo $cart_items_count[$id_cart] ?>"><?php echo $id_cart ?></td>
<?php $current_cart_id = $id_cart; ?>
<?php endif; ?>
<td> ?php echo $products ? </td>
<td> ?php echo $quantity ? </td>
<td> ?php echo $invoices ? </td>
<td> ?php echo $status ? </td>
</tr>
<?php foreach; ?>
</table>
没有"join"的变体:
SELECT ol.id_cart
,ol.products
,ol.quantity
,ol.invoices
,ol.status
FROM order_list ol
,member_carts mc
WHERE ol.id_cart = mc.id_cart
AND mc.customer = 'Klaudia'
尝试此查询:
SELECT *
FROM order_list AS ol
WHERE ol.id_cart IN (
SELECT id_cart
FROM member_carts
WHERE customer = 'Klaudia'
)
ORDER BY id_cart ASC