我做了一个查询,在其中我显示了loggedin电子邮件id的珍贵购买数据。我已经成功地完成了这项工作。现在我想根据日期显示数据。这里显示了每一行的日期。有什么建议吗!!
代码
<h3>My Orders</h3>
<table border="1">
<?php
$query="select orders.date,order_detail.quantity,order_detail.price,order_detail.color,order_detail.size,customers.name,products.product_name,products.product_image from order_detail JOIN orders on orders.serial=order_detail.orderid Join customers on customers.serial=orders.customerid Join products on products.productid=order_detail.productid where customers.email='$email'";
$sql=mysqli_query($con,$query);
while($row=mysqli_fetch_array($sql))
{
?>
<tr>
<td><?php echo $row['date'] ?></td>
<td><image width="80px" height="90px" src="images/images/<?php echo $row['product_image'] ?>"/></td>
<td><?php echo $row['product_name']. "*". $row['quantity']?></td>
<td><?php echo $row['color'] ?></td>
<td><?php echo $row['price'] ?></td>
<td><?php echo $row['size'] ?></td>
</tr>
<?php
}
?>
</table>
您尝试过根据日期对它们进行排序吗?这可以通过添加"ORDER BY column ASC
"来增加值或添加"ORDER BY column DESC
"来减少值来轻松完成!
$query="SELECT orders.date, order_detail.quantity, order_detail.price,
order_detail.color, order_detail.size,
customers.name,
products.product_name,
products.product_image
FROM order_detail
JOIN orders ON orders.serial=order_detail.orderid
JOIN customers ON customers.serial=orders.customerid
JOIN products ON products.productid=order_detail.productid
WHERE customers.email='$email'
ORDER BY orders.date";
查看手册
如果您想将相同的日期分组在一起,请尝试group BY命令(如果orders.date的类型为date/DATETIME/TIMESTAMP,则此示例有效):
$query="SELECT orders.date, order_detail.quantity, order_detail.price,
order_detail.color, order_detail.size,
customers.name,
products.product_name,
products.product_image
FROM order_detail
JOIN orders ON orders.serial=order_detail.orderid
JOIN customers ON customers.serial=orders.customerid
JOIN products ON products.productid=order_detail.productid
WHERE customers.email='$email'
GROUP BY DATE( orders.date )
ORDER BY orders.date";
如果您使用PHP时间戳或INT,则需要像这样转换日期:GROUP BY DATE( FROM_UNIXTIME( orders.date ) )
。
DATE(col)将日期/日期时间/时间戳转换为一天。GROUP BY将所有具有相同DATE的行放在一行中。现在,您可能还想将显示的数据更改为SUM(order_detail.price),以获取当天的总价,依此类推。