VB3.. SQL query problem

[obiwan8472]

Hi everyone,
I have a points system installed on my board board (VBr3).

I have several usergroups.. and a fair few public usergroups.

What I want to do is.. add up all the points from certain usergroups. I have an SQL query that does this ok. BUT the problem arises with the membergroupids when the lower numbers appear first.

Heres my current SQL.

SELECT SUM(points) FROM user WHERE usergroupid=13 AND membergroupids=13

usergroupid is fine.. that works. memergroups doesn't though because my test user has joined another group aswell.. so its in the table as 10,13,15.
It doesn't recoginise that he is number 13.. it just thinks his in 10.

Any ideas?
Thanks

[g-force2k2]

If you're just trying to extract the data per usergroup then you don't even need the:

PHP Code:

 AND membergroupids=13 


part of the query, because you're trying to find based on the usergroup not other membergroupids.

For instance if you're trying to find the sum for the Administrators usergroup the query will fail even if you add MySQL syntax to search the data of membergroupsid because it won't find the membergroupid of 6. If you're trying to incorporate membergroupids field then you should use 'OR' instead of 'AND'.

Hopefully that helps somewhat.

Cheers,
g-force2k2

[obiwan8472]

Hmmmm... the reason i am using membergroupid was because the members Primaryusergroup is Registered. I don't wish to sum that one up.
The ones i want to sum up are the Public Groups that the user ALSO belongs to.

As far as I can work out usergroupid is Primary Usergroup.. while membergroupid is the public or other usergroups that the user belongs to. In there you get listed 9,13,16 but the SQL i provided won't pick up that 13.. only the 9.
I want it to pick up the 9.
All my users are still in Registered.. but i am topping up their public usergroup bits.

[Xenon]

you have to do it this way:

[sql]SELECT SUM(points) FROM user WHERE usergroupid = 13 OR 13 IN (membergroupids)[/sql]

i think that should work

[obiwan8472]

Thanks for time. I tried it.. but keep getting back NULL. I know theres some points in the membergroup 13 cuz I checked. But it won't work
Thanks for your time anyway. Ill continue tinkering.