时间戳

首页 > 解决方案 > 如何在php中按日期（天）分组这个mysql值？

php - 如何在php中按日期（天）分组这个mysql值？

问题描述

我的 mysql 数据库中有这些表：

+------------+-----------------------+--------+
| date       | payment_id            | total  |
+------------+-----------------------+--------+
| 2019-05-02 |                    37 |     56 |
| 2019-05-02 |                    52 |     70 |
| 2019-05-06 |                    37 |     60 |
| 2019-05-07 |                    43 |     63 |
| 2019-05-14 |                    43 |     66 |
| 2019-05-16 |                    37 |     87 |
| 2019-05-16 |                    43 |     83 |
| 2019-05-21 |                    43 |    100 |
| 2019-05-22 |                    52 |     27 |
| 2019-05-22 |                    37 |     27 |
+------------+-----------------------+--------+

我的付款方式是：

+----+-----------------------+------------------------+
| id | type                  | value                  |
+----+-----------------------+------------------------+
| 37 | type_of_payment       | Paypal                 |
| 38 | type_of_payment       | Wire transfer 30 days  |
| 39 | type_of_payment       | Wire transfer 30-60days|
| 43 | type_of_payment       | Credit card            |
| 51 | type_of_payment       | Cash on Delivery       |
| 52 | type_of_payment       | Stripe                 |
| 53 | type_of_payment       | Rc Banc                |
+----+-----------------------+------------------------+

我的 php 查询是：

$query=QueryDB("SELECT date,total,value from table1 join table2 on table1.payment_id=table2.id where date between '2019-05-02' and '2019-05-06' order by date asc;");

其中 QueryDB 是我用于查询并从数据库返回值的方法。

php 对象的结果是：

Array
(
    [0] => Array
        (
            [date] => 2019-05-02
            [0] => 2019-05-02
            [total] => 56
            [1] => 56
            [value] => Paypal
            [2] => Paypal
        )

    [1] => Array
        (
            [date] => 2019-05-02
            [0] => 2019-05-02
            [total] => 70
            [1] => 70
            [value] => Stripe
            [2] => Stripe
        )

    [2] => Array
        (
            [date] => 2019-05-06
            [0] => 2019-05-06
            [total] => 60
            [1] => 60
            [value] => PayPal
            [2] => Paypal
        )

 )

第二个查询返回所有可用的付款方式

$paymentmethods=ObjectDB("select id,type,value from settings where type='type_of_
payment' order by value asc");

我有这个数组

Array (
[0] => Array
    (
        [value] =>Paypal                 
        [0] => Paypal
    )

[1] => Array
    (
        [value] => Wire transfer 30 days  
        [0] => Wire transfer 30 days  
    )

[2] => Array
    (
        [value] => Wire transfer 30-60days  
        [0] => Wire transfer 30-60days  
    )

[3] => Array
    (
        [value] => Credit Card
        [0] => Credit Card
    )

[4] => Array
    (
        [value] => Cash On Delivery
        [0] => Cash On Delivery
    )

[5] => Array
    (
        [value] => Stripe
        [0] => Stripe
    )

[6] => Array
    (
        [value] => Rc Banc
        [0] => Rc Banc
    )

)

我已经构建了我的 payment_methods_array

$payment_methods_array=array();
foreach($paymentmethods as $methods){
    $payment_methods_array[$methods['value']]=0;
}

我已经构建了所需的输出数组：

$days=array();
foreach($query as $day){
    $key=$day['value'];
    $date=$day['date'];
    $total=$day['total'];
    $payment_methods_array[$key]=$total;
    $days[$date]=$payment_methods_array;
}

输出数组是：

Array(
[2019-05-02] => Array
    (
        [Wire transfer 30 days] => 0
        [Stripe] => 70
        [Wire transfer 30-60days] => 0
        [Paypal] => 56
        [Rc Banc] => 0
        [Credit card] => 0
        [Cash on Delivery] => 0
    )

[2019-05-06] => Array
    (
        [Wire transfer 30 days] => 0
        [Stripe] => 70
        [Wire transfer 30-60days] => 0
        [Paypal] => 60
        [Rc Banc] => 0
        [Credit card] => 0
        [Cash on Delivery] => 0
    )
)

问题出在第二个数组中：查询结果中带条纹的付款为 0，但在输出数组中是前一个数组值 70。我可以解决这个问题吗？谢谢。

标签： phpmysql

解决方案

你$payment_methods_array一次又一次地重复使用。您需要将其值重置0为每天。但是 - 我宁愿整天用零初始化结果，然后只更改从数据库获得的值。

尝试以下操作：

$payment_methods_array=array();
foreach($paymentmethods as $methods){
    $payment_methods_array[$methods['value']]=0;
}

$days = [];

// init zero values
foreach ($query as $day) {
    $days[$day['date']] = $payment_methods_array;
}

// fill db values
foreach ($query as $day) {
    $days[$day['date']][$day['value']] = $day['total'];
}

结果：

array (
  '2019-05-02' => 
  array (
    'Paypal' => 56,
    'Wire transfer 30 days ' => 0,
    'Wire transfer 30-60days' => 0,
    'Credit Card' => 0,
    'Cash On Delivery' => 0,
    'Stripe' => 70,
    'Rc Bancv' => 0,
  ),
  '2019-05-06' => 
  array (
    'Paypal' => 0,
    'Wire transfer 30 days ' => 0,
    'Wire transfer 30-60days' => 0,
    'Credit Card' => 0,
    'Cash On Delivery' => 0,
    'Stripe' => 0,
    'Rc Bancv' => 0,
    'PayPal' => 60,
  ),
)

rextester.com 上的演示

这可能不是最有效的方式，但它非常简短和简单。

推荐阅读