fun with joins/count()

[ifm1989]

Ok, so I have two databases.

 

One is unlocks. One is unlocks_user.

 

Unlocks has columns like id,bonus1,bonus2, bonus3

 

unlocks_user has columns user_id,unlock_id

 

Im trying to make a function that counts your total bonus1, bonus2, bonus3 based off if you have unlocked it or not. I was able to accomplish this with a while statement, but I feel it would be much more efficient to use the mysql function count().

 

How would I do this?

 

This seems to return incorrect numbers:

 

$result = dbquery("SELECT count(fusion_unlocks.bonus1) AS bonus1

FROM fusion_unlocks,fusion_unlocks_user

WHERE (fusion_unlocks.id = fusion_unlocks_user.unlocks_id)

AND (fusion_unlocks_user.user_id = $user_id)");

[toplay]

Don't use count() in this case (the function SUM() is used to total things up). I assume bonus1-3 are integers, and if so just add the column values together. Example:

 

SELECT

          fu.bonus1 + fu.bonus2 + fu.bonus3 AS total_bonuses

  FROM

          fusion_unlocks_user fuu

  JOIN

          fusion_unlocks fu

    ON

          fu.id = fuu.unlocks_id

WHERE

          fuu.user_id = $user_id