Ignore duplicate values in PHP - MYSQL using Arraymap

Question

DB

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

Sirko · Accepted Answer · 2013-11-03 07:21:32Z

2

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

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.

answered Nov 3, 2013 at 7:21

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amal Prasad Over a year ago

thats not bad.. anyway thanx

unconnected · Accepted Answer · 2013-11-03 07:22:37Z

0

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

answered Nov 3, 2013 at 7:22

unconnected

1,0111 gold badge11 silver badges21 bronze badges

Comments

Krish R · Accepted Answer · 2013-11-03 07:39:30Z

0

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']
     );
   }

edited Nov 3, 2013 at 7:39

answered Nov 3, 2013 at 7:24

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

Collectives™ on Stack Overflow

Ignore duplicate values in PHP - MYSQL using Arraymap

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related