Adding multiple values from MySQL columns

[UndeadCircus]

Hey everyone,

 

I have another question that I'm sure is quite easy for someone else to figure out, but I'm having quite a bit of difficulty in getting a working solution. Here's what's going on...

 

I have two tables, one is for members and the other is for items owned by each member. The member's table, dumbed down, looks something like this:

 

Table: members

member_id

member_owner

member_item

 

The other table for the item, looks something like this:

 

Table: items

item_id

item_value

 

What I have going on is that each person who signs up to the website (this is a browser-based MMORPG) is in control of anywhere between 2-5 people. These are all held in the "members" table (different from the users table, where the 'member_owner' field would actually be the ID of the user that controls that character). Each of the people that the user controls has their own special item, which the "member_item" value points to the item_id value in the items table.

 

What's needing to happen is when the user goes into "Practice" on the website, the script will take the item_value of ALL of the items the member's are equipped with (so say member 1 is equipped with item_id 1 with item_value of 0.02, member 2 is equipped with item_id 2 with item_value of 0.03 and member 3 is equipped with item_id 3 with item_value 0.04). Those three item_values need to be totaled together and that is the strength of the entire group, 0.09.

 

This is always a dynamic value since each user can control a maximum of 5 people and a minimum of 2.

 

Do you guys think this should be in a while loop? I've though about using the SUM function in MySQL, but I don't see how it can return ALL of the item_value fields for each member.

 

I hope that wasn't too confusing and was in-depth enough to understand. I appreciate any and all help!

Thanks guys!

[kai555]

Hi.

 

using SUM is fine but you need to do a join to the members table

 

select sum(items.item_value) as total from items,members where members.item=itmes.item_id and members.member_owner=<place the owner you are summing for here>

 

 

if you want to get a list of all owners and their totals youl need a group by

 

select sum(items.item_value) as total,members.member_id from items,members where members.item=itmes.item_id group by members.member_id.

 

Hope this solves your problem.