I need help creating a sql table with a column for each day in the year?

[noniterum03]

I am new to SQL and databases in general. My problem is that I am trying to create a table that will store information about users in each row based on the date of that activity. So I need to create a table with 365 columns representing with one representing a day. Is there a way to automatically create this table without having to manually add 365 columns? Any ideas to get me started would be a big help.

Thanks,

[mwasif]

Why don't you create the table with 2 columns e.g. activity and activity_date. In activity column just save the data you wanted to store and populate activity_date with the date when the activity performed. If you want to save the current date then use MySQL's function CURDATE().

[drjohn]

Your way is just wrong, the method above is correct.

[griswolf]

One problam that many new SQL users have is getting rid of their incorrect ideas about how it works best. If you think of SQL as just another programming language like C, Java or Python, you will have a hard time: Most programming languages allow you to tell the computer not so much what you want as how you want it done (prescriptive programming). But SQL is more a logic language: You tell the database what you want, not what to do. So your job is to think about how to store all the information you will need (for that problem) in a way that makes sense.

In this case, you are going to want "what did the users do on a particular day", so the information you need to store is:

• which users there are
• what kinds of things users do
• what they do/with those things
• when they did any particular thing

So you are probably going to want

• a table users that has things like user_id, name, maybe email or photo or whatever
• a table user_actions that associates users with actions

and maybe that is enough, if the user_actions table has a timestamp and enough information about actions. Or maybe you will also want

• a table action which is about what kinds of things users do

Whether you want that is more about how "regular" actions are. If it something like a simplistic blog site, then probably you don't need the actions table. But if you can log in and out, post photos, post commentary, make connections, edit other people's posts, etc, then maybe you want to put the 'similar' parts in the separate table.