选择2个表中项目的总值并更新另一个TABLE的值（COMPLICATED QUERY）

Question

My case is that I want to compute a player atk power to be used on a battle module that i made, but just wondering i have actually 2 options:

Calculate Damage Dealt from the server.(my CURRENT OPTIONS)

1. use PHP to calculate DAMAGE DEALT and UPDATE server DATABASE values.
2. pass 2 of the chara id and just calculate all in the QUERY and UPDATE all(is this possible).

Question: Can I do it in a query?(option B)

my current set-up:
1 character has 4 items and i compute the characters atk by adding all 4 of the item atk and the chara base atk in the client side. (which i think is prone to security holes) and then update the values in the server side.

Here is my tables:

chara:

+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+
| chara_id | chara_name | chara_class_id | chara_level | chara_gold | chara_hp | chara_mp | chara_xp | chara_atk | chara_def |
+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+
|        1 | LawrenceX  |              1 |           5 |        230 |     -175 |     1000 |        0 |         7 |         3 |
|        3 | Viscocent  |              2 |           2 |         96 |     -206 |     1100 |     1700 |         5 |         5 |
|        4 | Piatos     |              1 |           1 |        120 |      -60 |     1000 |        0 |         7 |         3 |
|        5 | Hello      |              1 |           1 |        300 |      -50 |     1000 |      200 |         2 |         8 |
|        6 | Sample     |              3 |           2 |        251 |      -85 |      900 |        0 |         9 |         1 |
|        8 | Sampuro    |              2 |           1 |        170 |      895 |     1100 |      700 |         5 |         5 |
|       12 | fail       |              2 |           3 |        481 |     1100 |     1300 |        0 |        21 |         9 |
|       13 | new        |              1 |           1 |       1000 |      -80 |     1000 |        0 |         5 |         5 |
+----------+------------+----------------+-------------+------------+----------+----------+----------+-----------+-----------+

items:

+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-------------------------------------------------+------------+
|       0 | None            |         0 |        0 |        0 |        0 | pics/none.png                   |                                                 |        400 |
|       1 | Axe             |         1 |      220 |       10 |        0 | pics/weapons/axe.png            | Another lumberjack axe is another man's weapon. |        200 |
|       2 | Wooden Sword    |         1 |       70 |        0 |        0 | pics/weapons/wooden-sword.png   | A wooden sword, 99% made from wood              |        225 |
|       3 | Dagger          |         1 |       60 |        5 |        0 | pics/weapons/dagger.png         | A Dagger, Cheap and Sharp                       |         55 |
|       4 | Bow             |         1 |      120 |        1 |        0 | pics/weapons/bow.png            | The basics and simplest of all bows.            |        120 |
|       5 | Helmet          |         4 |        0 |       50 |        0 | pics/headgears/helmet.png       | iron helmet - made from an iron pot scraps.     |        155 |
|       6 | Tunic           |         2 |       10 |       10 |        0 | pics/armors/tunic.png           | A peasants tunic.                               |         50 |
|       7 | Armour          |         2 |        0 |       75 |        0 | pics/armors/armour.png          |                                                 |        150 |
|       8 | Necklace        |         3 |       25 |       15 |        0 | pics/accessories/necklace.png   |                                                 |        199 |
|       9 | Studded Leather |         2 |       25 |       60 |        0 | pics/armors/studded-leather.png |                                                 |        240 |
+---------+-----------------+-----------+----------+----------+----------+---------------------------------+-------------------------------------------------+------------+

equipment:

+----------+----------+-----------+-------------+----------+---------+
| equip_id | chara_id | weapon_id | headgear_id | armor_id | ring_id |
+----------+----------+-----------+-------------+----------+---------+
|        3 |        1 |        14 |           5 |        6 |       8 |
|        5 |        3 |         4 |           5 |        6 |       8 |
|        6 |        4 |        11 |           5 |        7 |       8 |
|        7 |        5 |        12 |           5 |        6 |       8 |
|        8 |        6 |         3 |          16 |        7 |       8 |
|       10 |        8 |        15 |           5 |        7 |       8 |
|       13 |       12 |        14 |           5 |        6 |      17 |
|       40 |       13 |         3 |           5 |        7 |       8 |
+----------+----------+-----------+-------------+----------+---------+

table relationships:

1 chara = 1 equipment  
1 weapon_id, armor_id, ring_id, headgear_id = 1 item (total of 4 items, headgear_id = 1 item).

I CAN GET THE EQUIPMENTS OF A CHARACTER BY USING THIS QUERY(KUDOS @JC):

SELECT i1.item_atk weapon_atk,i1.item_def weapon_def,
       i2.item_atk headgear_atk,
       i2.item_def headgear_def,
       i3.item_atk armor_atk,
       i3.item_def armor_def,
       i4.item_atk ring_atk,
       i4.item_def ring_def
       FROM equipment e LEFT JOIN
       item i1 ON e.weapon_id = i1.item_id LEFT JOIN
       item i2 ON e.headgear_id = i2.item_id LEFT JOIN
       item i3 ON e.armor_id = i3.item_id LEFT JOIN
       item i4 ON e.ring_id = i4.item_id 
       WHERE e.chara_id = 1

RESULTS:

+------------+------------+--------------+--------------+-----------+-----------+----------+----------+
| weapon_atk | weapon_def | headgear_atk | headgear_def | armor_atk | armor_def | ring_atk | ring_def |
+------------+------------+--------------+--------------+-----------+-----------+----------+----------+
|        275 |         25 |            0 |           50 |        10 |        10 |       25 |       15 |
+------------+------------+--------------+--------------+-----------+-----------+----------+----------+

now i want to total the atk and def of that character equipment and return it in that query

expected results:

+------------+------------+
| total_atk  | total_def  |
+------------+------------+
|        310 |        100 |
+------------+------------+

Answer 1

this is the simplest way that I can think of.

SELECT IFNULL(W.item_atk, 0) + IFNULL(H.item_atk, 0) + IFNULL(A.item_atk, 0) + IFNULL(R.item_atk, 0) AS total_atk
    , IFNULL(W.item_def, 0) + IFNULL(H.item_def, 0) + IFNULL(A.item_def, 0) + IFNULL(R.item_def, 0) AS total_def
    FROM equipment E
    LEFT JOIN item W ON W.item_id = E.weapon_id
    LEFT JOIN item H ON H.item_id = E.headgear_id
    LEFT JOIN item A ON A.item_id = E.armor_id
    LEFT JOIN item R ON R.item_id = E.ring_id
    WHERE E.chara_id = 1

I have renamed the aliases of tables to track them easily. And I used IFNULL in case the character has no particular equipment.

==================================================================================

Dude, I just made another query, I think this is faster than the one above. Though, I haven't tested them.

SELECT SUM(IFNULL(I.item_atk, 0)) AS total_atk
    , SUM(IFNULL(I.item_def, 0)) AS total_def
    FROM equipment E
    LEFT JOIN item I ON I.item_id = E.weapon_id
        OR I.item_id = E.headgear_id
        OR I.item_id = E.armor_id
        OR I.item_id = E.ring_id
    WHERE E.chara_id = 1
    GROUP BY E.chara_id