Combining database rows but not quite merging

[TheChaosFactor]

K, so my database has 1 table, 4 fields.  They are Name, Date, Money, Bricks.

 

What I need to do is check if Name and Date match. If they do, I need the money and bricks to be totalled, then all previous matching rows deleted.

 

So for instance if I have

 

Name        Date          Money      Bricks

Joe      2010-07-22      1000        0

Joe      2010-07-22      8000        2

 

 

I'd like it to turn them into one row that reads

 

Joe      2010-07-22      9000        2

 

Can I do this with a php array and loop with an if statement?  Like dump the whole table into an array, run the loop to check for dup names and dates and then have it do the math.  I was thinking I could then store the new values in a separate array, run a query to drop * where name = Joe AND date = 2010-07-22  then insert the manipulated values?

 

Or is there some magical mysql query that can perform these kinds of actions without all the extra steps?

 

[wildteen88]

Or is there some magical mysql query that can perform these kinds of actions without all the extra steps?

MySQL can do this using the GROUP BY clause, eg

SELECT Name, Date, SUM(Money) as total_money, SUM(bricks) as total_bricks FROM table_name GROUP BY Name, Date

 

You could actually do this with just MySQL. As MySQL does have the ability to insert the results from a query into another table, be it an existing one or a temporary one. Have a look into the SELECT INTO SQL statement for more info.