[SOLVED] PHP/MySQL Loop maths

[Tharkis]

Ok here's the situation.. I basicly need to take two tables and perform some math functions, I'll explain below..

 

There will be several groups. So what I want to do is iterate through the group names in table 1 and add the stipend amount for that group to any user who is assigned to that particular group. I am extremely new to php, and thusly I've never done anything like this before. Some help in explaining how I would go about solving this particular problem would be appreciated.

 

Table 1

Group Name Stipend Amount

Admin 100

Smith 50

Dog Catcher 75

Bum 5

 

Table 2

User namegroup name current money

Bob Smith 10

Jack Admin 37

Bill Bum 73

Fred Dog Catcher 17

James [/td] 31

 

The Result should be:

User namegroup name current money

Bob Smith 60

Jack Admin 137

Bill Bum 78

Fred Dog Catcher 92

James

 

[MadTechie]

you probably want to read up on MySQL (Joins are helpful)

see here

you could probably do it all in MySQL something like this.

SELECT *, (T1.StipendAmount+T2.currentmoney) as TotalMoney
FROM table2 T2 
JOIN table1 T1 on table1.GroupName =  table2.GroupName

 

i hope that help but MySQL is a large subject

 

EDIT: added the link

[Tharkis]

I hadn't realized you could do math in the sql statement.. Well really all I want to do is update the current money database field with the new value.. So could I do something like the following? Toss it in a loop based on the group names in table 1? I'm not sure this is valid code either.

 

UPDATE table2 SET currentmoney=(T2.currentmoney+T1.StipendAmount) WHERE GroupName='$CurGrpName'
JOIN table1 T1 on table1.GroupName =  table2.GroupName

[Tharkis]

Well I figured it out.. I'm thinking this post should probably be moved to the MySQL forum..

 

Here's what I came up with

 

UPDATE table1, table2

SET table1.currentmoney = (table1.CurrentMoney + table2.StipendAmount)

WHERE table1.GroupName = Table2.GroupName;

 

I tested it out in phpMyAdmin and it works exactly as expected..

 

This was way more simple than I thought or planned it to be. Thanks for the pointer MadTechie!