Adding values from columns
#1

Yo,

Got a quick question from your MySQL Experts. I am trying to calculate the total number items all my players have but i am pretty confused on how to do so, I am basically trying to add all the "Items" columns together and outputing the result but eventually my code doesn't work. I've tried using the SUM technique but no outcome. I am able to retrieve the Items column but dont know how to add them or print them out.
Reply
#2

It should work with SUM if your table is structured correctly. How is the table built up?

If it's built like "item1, item2, item3 ..." then that's not the correct way to do it, and SUM will not work. SUM only counts vertically through a single column, not horizontally. To normalize it correctly, you need at least one extra table, possibly two.

One table (let's say "player_items") with these two (or three) columns:
userid, itemid and optionally an amount

Another table can list the items, but this is not mandatory:
itemid, description, default_amount, ...

In "player_items" you then combine data from your main player and data from the item table. Then you will be able to count them correctly.
Reply
#3

There is only 1 row of "items" and it goes up by 1 everytime a player has a new item. I wanted them to count up vertically from each player and tell me how many items are there globally
Reply
#4

the concept should be the same for server side as the below

is this PHP or in-game?

PHP Version:
This is what you need (I use mysqli):

Code:
$count = mysqli_query($conn, "SELECT SUM(Items) FROM `table` WHERE `Items` > 0);
$sum = mysqli_fetch_array($count);

echo $sum['SUM(Items)'];
What mysql version are you using for your server?
Reply
#5

I am using blueg's r34, thats techinically where i got the idea of using SUM. If i follow that query , how am i supposed to transfer the result into a variable?
Reply
#6

The plugin version used really doesn't matter. You just fetch it like any other integer variable with cache_get_row_int. Row 0, field 0. You can also assign an alias (select ... as foo) and fetch it by name. Personal preference.
Reply
#7

Since i am trying to insert the result into 1 global variable, can you show me an example?
Reply
#8

Still need Help on this
Reply
#9

Figured it out, Thanks Vince!
Reply


Forum Jump:


Users browsing this thread: 4 Guest(s)