PHP/Mysql Insert Form

[Zeddicus]

Im developing an intranet website with an event calendar using MySQL, PHP and Javascript.

I have the calendar all set up and pulling test data from MySQL. So far everything looks good from that side.

I have a mysql database I called company and a table called eventcalendar. The table has columns for ID, Name, Description, Start_Date, End_Date and Current_Date. The date fields are just varchar fields because I want to be able to display the dates in the dd/mm/yyyy format rather than the yyyy/mm/dd format of a date field.

What I want is to come up with an insert statement that will post an individual row in mysql based on every date in the date range.

For example, if I have someone named Jim Smith who posts that he will be on vacation from april 25th through the 27th. The submission form and the mysql table should look like this.

Name: Jim Smith
Description: Vacation Day
Start_Date: 4/25/2016
End_Date: 4/27/2016

ID Name Description Start_Date End_Date Current_Date
1 Jim Smith Vacation Day 4/25/2016 4/27/201 4/25/2016
2 Jim Smith Vacation Day 4/25/2016 4/27/2016 4/26/2016
3 Jim Smith Vacation Day 4/25/2016 4/27/2016 4/27/2016



I know the start_date and end_date columns in the table aren't really necessary because I will be pulling that data to display in the calendar using the current_date field, but there shouldn't be any harm in having them there.

I've come up with a couple of separate loops. One puts multiple rows in sql based on the amount of days in the date range. The other loop increments the dates by adding a day each time the loop is run. They work great separately, but the problem is, I haven't been able to figure out how to get them to play nice together.

I was interested in seeing what others could come up with.

 

[Ken g6]

Seems like a lot more rows than you need. Why don't you just use the start_date and end_date fields?

 

[Leros]

I would put your dates into MySQL as Timestamp values and format them into strings in your application code.

 

[purbeast0]

as others have mentioned, there are multiple issues with what you're doing.

1. there is absolutely no reason to have 3 entries to let you know this guy is going to be on vacation for 3 days. there's no reason you need 14 rows with the exact duplicate data other than 1 "current_date" field that is different in each row, for someone going on vacation for 14 days.

2. all you need is the start date and end date as mentioned. "current_date" is misleading because it's not actually the current date. if it was the current date, the following day it would be incorrect because it would be yesterday.

3. also as mentioned, use timestamps and create a date object from the timestamp on the client/server, and then when you display it, just use a date formatter to format it in the MM/DD/YYYY format you want.

4. in general you want to have a few rows in your database as you can to get the job done. you gain absolutely nothing by having 3 rows in the database for a guy going on vacation for 3 days, when you could just have 1 row to know that information. what if you had 1000 people going on vacation for 2 weeks? you could have 1000 rows if each just had 1 row, or you could have 14000 rows (if including weekends). while that is still very trivial amounts of data and not realistic, you can see that over time it will get much larger much quicker. it's better to do it right from the beginning rather than doing it a hacky way just because it feels easier or something.

 

[Zeddicus]

Thanks for the replies.

To answer some questions. I am aware that it would be better to just use the start and end date fields and have a submit form entry end up all in one row. My question really was more how can I accomplish this and not so much is this the best way.

The multiple rows for each day of vacation is to solve something on the calendar display end at least for now and it's going to be harder to fix.

This really is development and not production at this stage. Even if I were to do it this way, we are not talking 1000 people going on vacation for x many days thereby inserting tons of unnecessary data. This will probably be used by 10-15 people, so the extra data isn't really an issue. I could always purge the expired entries in MySQL from time to time anyway. Right now this is more a learning exercise to see what can be done. I also may have a more practical reason to apply something like this in the future. You never know.

Oh, also, the current_date field doesn't represent the actual current day. What it represents is the current day as it displays on the calendar. I could call it display_day or something else. No big deal either way as long as I know what it is.

Anyway, I solved the problem. I don't need time stamps. I can do it by inputing my dates as mm/dd/yyyy strings and converting those stings to dates in the m-d-Y format later.

I have 2 loops. One inside the other. The first loop calculates the difference between the end date and the start date and increments itself based on that total.

The second loop increments the date by 1 day and shoots that data into MySQL as many times as the first loop runs. The main thing to solve the problem I had was to move my query to this second loop.

 

[beginner99]

Thanks for the replies.

To answer some questions. I am aware that it would be better to just use the start and end date fields and have a submit form entry end up all in one row. My question really was more how can I accomplish this and not so much is this the best way.

The multiple rows for each day of vacation is to solve something on the calendar display end at least for now and it's going to be harder to fix.

This really is development and not production at this stage. Even if I were to do it this way, we are not talking 1000 people going on vacation for x many days thereby inserting tons of unnecessary data. This will probably be used by 10-15 people, so the extra data isn't really an issue. I could always purge the expired entries in MySQL from time to time anyway. Right now this is more a learning exercise to see what can be done. I also may have a more practical reason to apply something like this in the future. You never know.

Oh, also, the current_date field doesn't represent the actual current day. What it represents is the current day as it displays on the calendar. I could call it display_day or something else. No big deal either way as long as I know what it is.

Anyway, I solved the problem. I don't need time stamps. I can do it by inputing my dates as mm/dd/yyyy strings and converting those stings to dates in the m-d-Y format later.

I have 2 loops. One inside the other. The first loop calculates the difference between the end date and the start date and increments itself based on that total.

The second loop increments the date by 1 day and shoots that data into MySQL as many times as the first loop runs. The main thing to solve the problem I had was to move my query to this second loop.

And people wonder why a lot of software plain sucks. OK, you seem to be learning and it's one fo your first tries. So mistakes will be made. my advice is to take the advice of more experienced programmers instead of dismissing it.

Storing dates as string is lazy and issues will happen. Tell me how you search for persons that are away form May 2nd to may 4th for example? This is trivial using date/timestamp column.

Development doesn't mean anything once it runs it will be used and not changed unless really necessary. You already provided the proof. An issue like yours having to store n-rows per n-days of vacation screams there is a very serious design issue in your application.

In your case it's probably simpler if all those users just create a gmail address and use google calendar.

 

[Ken g6]

OK, how's this strike you?

1. Make a table called eventcalendar with ID, Name, Description, Start_Date, and End_Date, but not Current_Date.
2. Make a second table called eventcalendardates of all the possible dates.

3. CREATE VIEW eventcalendarview AS
   SELECT ID, Name, Description, Start_Date, End_Date, date AS Current_Date 
   FROM eventcalendar, eventcalendardates
   WHERE date >= Start_Date AND date <= End_Date

This makes the larger table exist from the data in a more concise table. There's probably some way to replace the table of all the possible dates; but I haven't figured it out yet.

Edit: By the way, if you're going to store dates as strings, I recommend the YYYYMMDD format, as it makes sorting easier.

 

[purbeast0]

Thanks for the replies.

To answer some questions. I am aware that it would be better to just use the start and end date fields and have a submit form entry end up all in one row. My question really was more how can I accomplish this and not so much is this the best way.

The multiple rows for each day of vacation is to solve something on the calendar display end at least for now and it's going to be harder to fix.

This really is development and not production at this stage. Even if I were to do it this way, we are not talking 1000 people going on vacation for x many days thereby inserting tons of unnecessary data. This will probably be used by 10-15 people, so the extra data isn't really an issue. I could always purge the expired entries in MySQL from time to time anyway. Right now this is more a learning exercise to see what can be done. I also may have a more practical reason to apply something like this in the future. You never know.

Oh, also, the current_date field doesn't represent the actual current day. What it represents is the current day as it displays on the calendar. I could call it display_day or something else. No big deal either way as long as I know what it is.

Anyway, I solved the problem. I don't need time stamps. I can do it by inputing my dates as mm/dd/yyyy strings and converting those stings to dates in the m-d-Y format later.

I have 2 loops. One inside the other. The first loop calculates the difference between the end date and the start date and increments itself based on that total.

The second loop increments the date by 1 day and shoots that data into MySQL as many times as the first loop runs. The main thing to solve the problem I had was to move my query to this second loop.

no offense but this is a terrible solution to such a trivial problem. you've completely over complicated things by trying to do things the wrong way out of convenience and your will to not want to learn how to do things properly. what you have on hand is a maintenance nightmare.

you say this is a learning exercise, but it's clearly not since you're stuck in your bad development ways.

best of luck to you.

 

[sao123]

I also may have a more practical reason to apply something like this in the future. You never know.

Actually we do know... and you never will... or never ever should...

whatever the flaw in your calendar you are causing your database to be under-normalized and non-relational, and will eventually result in ambiguities and or duplication in your data. this is just a bad practice all together.


What is the flaw in the calendar, we might be able to help you solve that so your database can be used properly.

 

[Jaskalas]

The error on the Calendar display is clearly based on it only populating a single day per DB entry. Which case you solve that not by corrupting your database, but by manipulating the resulting data after you pull it from the database.

Use PHP to format it for calendar use.

Speaking of formatting, store the dates properly and then use date with strtotime, like this:

$Start_Date = '2016-05-10';
$Formatted_Date = date('d/m/Y', strtotime($Start_Date));