Wow.... this is something else. I am doing a project, very inefficiently, obviously, because I keep running into timeout problems.
Right now, for every user in my game, I am creating a new table (potentially thousands of tables in the db). Then, there is a column for each asset the user has in the game.
Then there are 24 rows in each table, one for each hour. Currently I have php looping through each individual user's table each hour. What I am doing is adding the second row in each table to the first, deleting the second row, then adding another row at the end (so that it sorts properly when I call it). Note that the first hour is the total amount of that particular asset that they have. Unfortunately, the more users I add (ie - the more tables I add), the longer it's taking to execute this query, and I'm running into timeout issues.
So, what I plan on doing is something different, but that is why I am here... 'cuz I am ever so shy of figuring it out. I want to create one table for each asset instead, then have 25 columns (one representing each hour, and one for the user id), and then having one row for each user.
Assuming column 1 is the user, column 2 is the first hour, column 3 the second hour, etc...:
What I need to know is how I can make MySQL add column 3 to column 2 (giving me the total amount of that particular asset for that particular user for that update), then shift the values of the 4th column, to the third column's position, 5th to the fourth, etc., and then have 25 the 25 column equal 0.
And this needs to be done very efficiently.
I've attached a small example on a notepad so you guys can see what I'm actually trying to do (since it doesn't paste well here - and it helps the explanation dramatically).
I'm begging some MySQL expert to help me out here, as it's the life or death of my game...
