I have made a query in which i am displaying precious purchased data of the loggedin email id. I have done this successfully. Now i want to display data according to date. Here it shows date with each row. Any suggestions please!!
Code
<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>
Have you tried sorting them according to date? This is easily done by adding "ORDER BY column ASC" for increasing values or "ORDER BY column DESC" for decreasing values!
$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";
Have a look in the manual
If you want to group same days together, try the GROUP BY command (this example works if orders.date is of type 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";
In case you use PHP timestamp or INT, you need to convert the date like this: GROUP BY DATE( FROM_UNIXTIME( orders.date ) ).
DATE(col) converts a date/datetime/timestamp to a single day. GROUP BY puts all rows with the same DATE into one row. Now you might want to change the data displayed too to SUM(order_detail.price) to get the total price for that day, and so on.