PHP merge several rows if duplicate value in cells

Question

Im fetching rows out of a MySQL database. Each row contains a date and an amount (USD). Some of the dates are identical. I've been trying for hours to merge the amount of those rows with identical dates.

I have tried array_merge as well as serveral if-statements, foreach as well as while.

while($row2 = mysqli_fetch_array($result3)) {

  array_push($mergeDuplicateDate,
    array(                                   
      $row2[1], //date                                                                           
      $row2[2] //amount
    )
  );

};

If the output looks like:

06-25-2019, $5

06-25-2019, $10

06-26-2019, $2

06-26-2019, $1

I want it be summarized like:

06-25-2019, $15

06-26-2019, $3

I know it can't be that hard to do with PHP but I'm not super expirienced at programming. Thus far I could google up any problem that I came across. This time I just can't seem to find an answer. I really don't even know where to start solving this issue.

Thanks in advance to whoever breaks this down to me!

Make an associative array whose keys are the dates. Loop through the data and add each amount to the element with that key. — Barmar, Jun 27 '19 at 17:02
SHow us the query you are using to get this from the database. It would be much easier done as part of that query — RiggsFolly, Jun 27 '19 at 17:02
Why don't you do it in the query? `SELECT date, SUM(amount) FROM table GROUP BY date` — Barmar, Jun 27 '19 at 17:03
I hope you don't really have `$` in the `amount` fields. You should store them as numbers, and add that when printing. — Barmar, Jun 27 '19 at 17:04
Judging by the example @Barmar the `sum()` may need to get rid of the `$` first :) — RiggsFolly, Jun 27 '19 at 17:04
@barmar _I hope you don't really have $ in the amount fields_ But deep down where the sun does not shine you know they do :) — RiggsFolly, Jun 27 '19 at 17:05
@RiggsFolly I'll just renewed the prescription on my rose-colored glasses :) — Barmar, Jun 27 '19 at 17:06
See https://stackoverflow.com/questions/35473174/creating-one-array-from-another-array-in-php for how to group array data. — Barmar, Jun 27 '19 at 17:09
@Barmar - The assoc array was the way to go for me! Problem solved. Thank you very much. Would upvote the comment if I only knew how ¯\_(ツ)_/¯ — user2654412, Jun 27 '19 at 18:20

score 0 · Answer 1 · edited Jun 27 '19 at 19:02

0

Use a database, it is the least computationally expensive way to solve your problem. XD

SELECT date ,SUM(amount) FROM table GROUP BY date ASC

edited Jun 27 '19 at 19:02

joeybab3

295
2
7
24

answered Jun 27 '19 at 17:28

Hudson Cantuária

51
4

Ever since I LEFT JOIN'd multiple databases to begin with it seem to hard for my current skill level to figure out if and where I could include SUM. I've went with the PHP assoc array and it worked like a charm :) – user2654412 Jun 27 '19 at 18:21

score 0 · Answer 2 · answered Jun 27 '19 at 17:29

Best way to do this is using group by in MySQL query mentioned by @barmar in comments.

If you really want to do it in PHP you can use below code

$finalArray=[];
while($row2 = mysqli_fetch_array($result3)) {
  $finalArray[$row2[1]] = $finalArray[$row2[1]] ? $finalArray[$row2[1]] +$row2[2] : $row2[2];
};

PHP merge several rows if duplicate value in cells

2 Answers2