Sum of other cols

[turt2live]

Hello,

I have a table that looks like this:

+----+-------------+-------+-------+-----+
| ID | Player Name | Col 1 | Col 2 | ... |
+----+-------------+-------+-------+-----+
| 01 |  Some Guy   |   2   |   3   | ... |
+----+-------------+-------+-------+-----+

the ... represents a (technically) unknown amount of columns.

What I want is to select the row where Player Name matches my search, and get the sum of the Col 1 -> Unknown column values.

So... something like

SELECT SUM(Col1 -> Unknown) WHERE `Player Name`='$name';

Any help is appreciated.

-- Turt2Live

[debasisdas]

for that you need to frame the query dynamically at run time.

try this.

PREPARE sql_stmt FROM "your query";
EXECUTE sql_stmt;

[turt2live]

How would that be represented in PHP?

[Brillig]

This might not be answering your question the way you wish, but you should never design a table like that. Whatever values are in col1, col2,... colx should be in a separate table that joins back to the players with a foreign key.

Any time you have a table with any variable structure it should be a huge red flag that there is a problem with your datamodel.

[turt2live]

I was creating a web-tool for an online game where each zone was a new table. The columns represented NPC interactions (for tracking). The script would then figure out which zone is most active.

I was hoping there was an easier way to use SUM() without typing each column as a giant addition statement.

Would there be an easier way to store this data? Or am I SOL in terms of long lines of code?

[Brillig]

Have a Player table with PlayerId and Name. Then create an interaction table, dependent on your site, but probably with InteractionId, NPCID, InteractionCount, PlayerId.

PlayerId would be a foreign key back to the player table and NPCID would be a foreign key to a table of NPCs.

Then you can sum up the interactions across all NPCs for a player simply with:

select sum(InteractionCount) from Interaction where PlayerId = x

This would also make your code that inserts/tracks this data much simpler, rather than changing table structures each time. The table structures would stay the same and you would just be inserting rows.

[turt2live]

Very Helpful!

Thank you so much!