php - 使用 MYSQL/PHP 显示从现在到 x 天前的每日总计

Question

我有一个名为“订单”的表，它包含； id、order_total 和时间字段。 'time' 是一个整数并存储一个 unix 时间戳...

订单

| id | order_total  | time          |-------------------------------------| 1  | 42.00        | 1443355834    || 2  | 13.00        | 1443460326    || 3  | 51.00        | 1443468094    || 4  | 16.00        | 1443477442    || 5  | 10.00        | 1443606966    || 6  | 53.00        | 1443608256    |

I want to able to display in a table using php the sum, of 'order_total' for each day for the previous 'x' amount of days (or weeks or months) so it will look something like this:

| Date      | Order Total |---------------------------| 27/09/15  | 42.00       || 28/09/15  | 80.00       || 30/09/15  | 63.00       |

I have made a MYSQL query and a php loop that kind of works but being new to MYSQL I am probably over-complicating things and there must be an easier way to do this ? When I say kind of works, it will correctly sum and show the order_totals up until the current day but for some reason will combine the current day with the previous day.

Here is what I currently have:

$x = $interval;
$y = $x - 1;

while ($x > 0) {
    $sql10 = "
        SELECT id,
               time,
               SUM(order_total) as sum,
               date_format(DATE_SUB(FROM_UNIXTIME($now_time), INTERVAL $x DAY), '%Y-%m-%d') as thedate  
         FROM $ordersTABLE 
        WHERE FROM_UNIXTIME(time) BETWEEN date_format(DATE_SUB(FROM_UNIXTIME($now_time), INTERVAL $x DAY),'%Y-%m-%d') 
          AND date_format(DATE_SUB(FROM_UNIXTIME($now_time), INTERVAL $y DAY),'%Y-%m-%d')";

    $result10 = mysql_query ( $sql10, $cid ) or die ( "Couldn't execute query." );

    while ( $row = mysql_fetch_array ( $result10) ) {
        $order_total = $row ["order_total"];
        $thedate = $row ["thedate"];
        $sum = $row ["sum"];
        $sum = number_format($sum,2);
        $thedate = strtotime($thedate);
        $thedate = date("d/m/y",$thedate);

        print "<tr><td width=\"120\">$thedate</td><td>\$$sum</td></tr>";
    }

    $x--;
    $y--;
}

(字符串 $now_time 包含作为 Unix 时间戳的当前时间，因此无法更改系统时间的转换，并且它包含用户的正确本地时间)

有更好的方法吗？

Answer 1

您可以使用 FROM_UNIXTIME 将时间戳转换为 YYYY MM DD功能，然后仅选择那些足够旧的功能，这要归功于 DATEDIFF功能。今天的日期由 CURDATE 提供功能。

首先，检索早于时间间隔的订单的总计并重新格式化日期字段的查询:

$q1 = "SELECT " . $ordersTABLE . ".order_total AS total, FROM_UNIXTIME(" . $ordersTABLE . ".time, '%Y-%m-%d') AS short_date FROM " . $ordersTABLE . " WHERE DATEDIFF(CURDATE(), short_date) > " . $intervalInDAYS;

然后，总结一天的总数:

$q2 = "SELECT short_date AS day, SUM(total) AS total FROM (" . $q1 . ") GROUP BY short_date";

然后执行存储在 $q2 中的查询以及显示结果所需的所有其他操作。
查询结果应采用以下形式:

|    day    |    total    |
===========================
| 25/09/15  |  34.00      |
| 16/09/15  | 100.00      |
| 31/07/14  |   3.20      |
|    ...    |     ...     |