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

5 Years
Discussion Span
Last Post by turt2live

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

try this.

PREPARE sql_stmt FROM "your query";
EXECUTE sql_stmt;

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.

Edited by Brillig: n/a


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?


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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.