Hi.
You should never have to dynamically alter your table structures.
Having multiple columns per row to store the same kind of data is also not a good idea.
You don't actually have to create 50 fields for each week of the year.
You simply create 50 fields and record
when the current row was added.
Then you can simply use that data to sum up the data collected for each week.
For example, if I owned a casino and I wanted to record each time a slot machine gave out a jackpot, I might do something like:
-
CREATE TABLE SlotJackpots (
-
EntryID Int Primary Key Auto_Increment,
-
SlotID Int Not Null,
-
Amount Float Not Null,
-
Created TimeStamp Not Null
-
);
-
Where a new row would be added each time a machine gave a jackpot.
Then, to get a list of the jackpots and winnings given by a single machine for every week of the year, I could do:
-
SELECT
-
WEEKOFYEAR(Created) AS `Week #`,
-
SUM(Amount) AS `Total Amount`,
-
COUNT(SlotID) AS `Total Jackpots`
-
FROM SlotJackpots
-
WHERE SlotID = 1
-
GROUP BY `Week #`;
-
Which might return something like:
-
+--------+--------------+----------------+
-
| Week # | Total Amount | Total Jackpots |
-
+--------+--------------+----------------+
-
| 47 | 100 | 2 |
-
| 48 | 140 | 2 |
-
+--------+--------------+----------------+
-
It's obviously not a perfect design, but you get the point?
