Merge duplicates into one row and sum up values using php

Question

I have a table that hows the following rows...

ID     | TITLE   |   ARTIST    |   ADDED               |  HITS | HITS THIS WEEK
--------------------------------------------------------------------------------
7505   | Track 2 |   Artist 1  |   2009-08-24 10:32:41 |  1539 |  2
7483   | Track 2 |   Artist 1  |   2009-08-23 16:58:35 |  1450 |  3
7324   | Track 1 |   Artist 1  |   2009-08-14 11:28:18 |  5291 |  1
7320   | Track 1 |   Artist 1  |   2009-08-14 10:24:17 |  2067 |  1

What I want to do is is have the the duplicates add up the hits and hits this week and display as one row.

I want to fetch the mysql data as it is (with the duplicates), and then using php i wanna sum up the duplicates as one row for echo'ing.

Can someone tell me how I would go about this?

EDIT:

Heres my current sql query...

SELECT `files`.`file_id` , `files_meta`.`title` , `files_meta`.`artists` , `files`.`added` , `files`.`uniquehits` , `files`.`uniquehits_week` , `files_meta`.`filename`
FROM promo_artists
INNER JOIN promo_files ON promo_artists.id = promo_files.artist_id
INNER JOIN files ON files.file_id = promo_files.file_id
INNER JOIN files_meta ON files.file_id = files_meta.file_id
WHERE username = '$username' ORDER BY `files`.`added` DESC

Jonathan Leffler · Accepted Answer · 2009-09-29 02:47:01Z

2

I'd be inclined to do it in SQL:

SELECT track, artist, SUM(hits) AS hits, SUM(hits_this_week) AS hits_this_week
  FROM Atable
 GROUP BY track, artist;

This saves having to do the computation in PHP, and reduces the traffic between PHP and the DBMS.

answered Sep 29, 2009 at 2:47

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

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

2 Comments

MaxiWheat Over a year ago

From what I understand about the question, he wants to fetch the data as it is (for some reason that we ignore) : "I want to fetch the mysql data as it is (with the duplicates), and then using php" I don't agree with this statement and your solution seems right to me, but maybe Imran could explain why he wants the data as it is ?

Jonathan Leffler Over a year ago

Yes - I'm deliberately proposing an alternative solution. If he does want to do it in PHP, that's fine; my solution does not then apply. I think Adam Frisby's solution might apply when tarted up a bit; I don't see how his code splits the row into bits, or handles hits vs hits this week.

Adam Frisby · Accepted Answer · 2009-09-29 02:33:48Z

0

foreach($rows as $row) {
 $data[$artist][$track] += $hits;
}

?

answered Sep 29, 2009 at 2:33

Adam Frisby

3675 silver badges20 bronze badges

Collectives™ on Stack Overflow

Merge duplicates into one row and sum up values using php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related