忽略PHP中的重复值 - 使用Arraymap的MYSQL

Date        Price  qty
2012-10-21  $11 150
2012-10-22  $12 90
2012-10-22  $12 10
2012-10-23  $13 250

CODE

$result = mysqli_query($con, "SELECT * FROM table ORDER BY 'date'");

    while ($row = mysqli_fetch_array($result)) {
        $orders[] = array(
            'Date' => $row['date'],
            'price' => $row['price'],
            'Quantity' => $row['qty']
        );
    }

I don't need to get duplicate dates and for those duplicate dates need to get Average price and sum of Qty like below example:

我不需要获取重复日期,并且对于那些重复日期需要获得平均价格和数量总和,如下例所示:

EXAMPLE

Date        Price  qty
2012-10-21  $11 150
2012-10-22  $12 100
2012-10-23  $13 250

I try to use Array-map but i fail on that help is really appreciated

我尝试使用Array-map,但我失败了,真的很感激帮助

3 个解决方案

#1

I'd suggest to move the problem to the database by using this query using GROUP BY and the respective aggregation functions:

我建议使用GROUP BY和相应的聚合函数使用此查询将问题移动到数据库:

SELECT `date`, AVG( `price` ), SUM( `qty`)
FROM `table`
GROUP BY `date`
ORDER BY `date`

In general, it is more advisable to let MySQL do such work, than first transmitting the data to your application and rearrange it there.

一般来说,让MySQL做这样的工作比将数据首先传输到应用程序并在那里重新排列更合适。

#2

Change you query

改变你的查询

SELECT Date, AVG(Price), SUM(Qty) FROM table
GROUP BY Date

Or change php to check if date in array and calculate averages and sums

或者更改php以检查数组中的日期并计算平均值和总和

#3

Can you try this,

你能试试吗

   $result = mysqli_query($con, "SELECT date, AVG(Price) as price, SUM(qty) FROM table GROUP BY date ORDER BY date");

   while ($row = mysqli_fetch_array($result)) {
    $orders[] = array(
        'Date' => $row['date'],
        'price' => $row['price'],
        'Quantity' => $row['qty']
     );
   }

#1