在不同的表中根据日期显示数据

Question

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> 

Answer 1

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.