How to create a table with a row for each day of a given year?

Question

I need to develop a calendar which tells you if a given day is of type A, B, C or D. So basically, users requests info for a given day, and the website tells them if that day is of type A, B, C or D. So I will create a PHP function that checks for the given day in the database, and returns its type.

Is it possible (and if it is, is it a good practice) to programatically create the mysql table with a row for each day of the year, with a DATE type to identify each day? How can I do this?

EDIT: In the end, I used @nic's idea but instead I coded it in PHP, and created a reusable function with it. The function expects a string parameter, containing the year in YYYY format, and creates a table with a row for each day of the year cointaining a DATE field, and several text fields for day, month, year and weekday.

You can find it here

See here for how to create a calendar table : http://stackoverflow.com/questions/5844361/data-warehouse-for-ad-dates/5845900#5845900 — nos, Jun 06 '11 at 13:17
Is the process for determining a date's type something that can be expressed algorithmically, or is it just a mapping defined by a human? Is the same day, i.e. June 4th, the same type regardless of the year? What happens to days after Feb 28th in leap years? If it's a human-defined non-algorithmic mapping then a table is the right solution. If the date type can be calculated, you shouldn't store a table. — Jim Garrison, Jun 06 '11 at 14:48
@Jim, it is a mapping defined by a human by a backend, so by your logic a table should be OK... — agente_secreto, Jun 06 '11 at 15:17

score 0 · Answer 1 · edited May 23 '17 at 12:26

0

I would say yes, it's a good practise. You could also have a number table in your database. It can be handy in certain situations.

Check Ronnis' answer for how to create a Calendar table, like the one you want. By number table, I mean exactly the numbers table he creates.

You could store the dates for many years, like a hundred years. This would mean a table with only about 36500 rows.

edited May 23 '17 at 12:26

Community

1
1

answered Jun 06 '11 at 13:01

ypercubeᵀᴹ

113,259
19
174
235

What do you mean by a number table? (sorry if its a dumb question) – agente_secreto Jun 06 '11 at 13:07

score 0 · Accepted Answer · answered Jun 06 '11 at 17:31

In the end, I used @nic's idea but instead I coded it in PHP, and created a reusable function with it. The function expects a string parameter, containing the year in YYYY format, and creates a table with a row for each day of the year cointaining a DATE field, and several string fields for day, month, year and weekday.

You can find it here

How to create a table with a row for each day of a given year?

2 Answers2