setting values to variables from a db..

pleo

ok here's my situation

I have a bunch of users in my database and attached to each user is a subscription level (Basic, Supporter, Supporter*, Honorary, ect). I want to be able to set numerical values to each level. So like Basic = 22, Supporter = 30, and so on. Then i want to take all of the vaules in the database, assign my numerical values to them and then add them all up to I get a sum. Can anyone help me with this? I'd really appreciate it. thanks

procoder

Quite dont understand what you mean

so you have lets say

Bubba, 22
Moo, 23

and now you want a query to add all the second field values?

select SUM(field_name_of_values) as total_sum from table

laserlight

I think you have to make clear the relation ship between users and subscription levels.
If each user has only 1 subscription level, then create 2 tables.
1 table will have the subscription level names and the number assigned to it, along with a primary key, presumably a level_id
the other table, for users, will be linked to this one by the level_id

To get the sum, you could then sum from the levels table based on level_id corresponding to each user in the users table

pleo

well what i mean is, i have 4 different type of levels of users depending on how much money they contibuted. so if they contributed 22 bucks then they are a "Basic", 30 dollars is a "Supporter" ect. i want to get the total ammount of money that has been colected so far by assigning values to the fields "Basic", "Supporter", ect that are in my database.

procoder

LOL you jsut got me more confused

Ok so when somone donates it updates a entry in the field and adds whatever they added to there record, right?

how are you going to asign to 'basic' fields if its not a field...

bah

give us your currecnt database schemetic

in your script you do a mysql query, then a php if-then-esle

if moneytotal is 22 then user is basic, else he is this and bla bla.

tekky

your level table would look like this

level_id = primary key
level_name = text (Basic/Supporter)
level_mark = 22 or 30 or 50

then do

SELECT SUM(COUNT(usertable.user_id)*leveltable.level_mark), usertable.level_id, leveltable.level_mark FROM usertable, leveltable WHERE leveltable.level_id = X and usertable.level_id = leveltable.level_id

(I think that SELECT all be it long would work, could prolly do that with a JOIN easier... but not that into thinking right now)

this should count the users at said level, say 5 at basic, and multiply by 22 and return that as $row[0]

pleo

ok sorry, i am making this way more complicated than it really is. I'll break it down. here is a simlified version of what my database would look like

Ok to i have three users that are "Basic" and two that are "Supporter", so.. i want my script to do this for me

Basic = 22
Supporter = 30

of "Basic" users = 3

of "Supporter" users = 2

3(22) + 2(30) = 126

and "126" is the number i am trying to reach. does that make sense?

laserlight

Originally posted by pleo
well what i mean is, i have 4 different type of levels of users depending on how much money they contibuted. so if they contributed 22 bucks then they are a "Basic", 30 dollars is a "Supporter" ect. i want to get the total ammount of money that has been colected so far by assigning values to the fields "Basic", "Supporter", ect that are in my database.

then use my solution for table structure.
you should create 2 tables, since different users can have the same level.

In fact tekky gave the code snippet with that sort of structure in mind.

pleo

there's gotta be an easier way. Can i just have my script connect to my DB, retrive all the vaules of my "subscription levels" and assign values to them and then add them all up? It doesn;t seem that complicated but i'm just not sure how to impliment it.

laserlight

of course you can, by storing the values with the user details on the fly.

but the trade off there is that you'll have lots of duplicate values, and if you decide to change the level settings, you may have to do a bit of work.

tekky

ack i was way off I believe....
so do something like I said with the level_id table othewise you will have to hard code your values in each page dealing with them

and your query would be like

SELECT u.user_id as uid, u.level_id as ulevel_id, l.level_id as lid, l.level_name as lname, l.level_mark as lmark, sum(count(u.user_id)*l.level_mark) as total FROM usertable AS u, leveltable AS l WHERE u.level_id = l.level_id GROUP BY l.level_name

if I am thinking right....
this would give you values like

$row['uid] = 1
$row['ulever_id] = 1
$row['lid'] = 1
$row['lmark'] = 22
$row['lname'] = "Basic"
$row['total'] = 110 (because I'm randomly picking we have 5 basic members)

again I could be wrong... and making that a lot harder than it seems... maybe laser can correct me?

laserlight

Table: levels
level_id
name
number

Table: users
user_id
level_id
name

I would try a:
SELECT SUM(levels.number) FROM levels, users WHERE levels.level_id=users.level_id
Though my SQL is not good so this could actually be wrong.

A long winded way is:

//get the level_id of all the users
$query = db_query("SELECT level_id FROM users");
$sql = db_query("SELECT level_id, number FROM levels");

//get the number corresponding to each level
while ($row = db_fetch_array($sql)) {
	$key = $row['level_id'];
	$level_details[$key] = $row['number'];
}

$total = 0; //the sum
while ($row = db_fetch_array($query)) {
	$key = $row['level_id'];
	$total += $level_details[$key];
}

pleo

thanks for the help, but i found an easier way to do it. Thanks anyway!