Thursday, May 23, 2013

Storing and Retreiving Dates - PHP

I had a bit of free time over the past week, and I started working on the final section I promised to convert to CMS for the CESAC website. This section involves the annual career fair, and to save down on the number of variables I looked for static relationships. The first thing that jumped out at me were dates.

For each career fair, there are four dates that need to be dealt with. Early registration, company info sessions, career fair, and post-fair interviews. The nice thing about these is that the latter three have a static relationship. The info sessions are always the day before the fair, and the interviews are the day after. Really then, only two dates need to be stored. This does pose an additional challenge of finding the next day and previous day of a specified date, but it's worth it to cut the inputs down by half. Less to store, less code, and less chance for user screw up.

The question then became, how do I best store the dates to save space and use the most efficient code in conversion for display. In previous sections of the CESAC website I'd stored dates as three separate numbers. Example:

September 18th, 2013 becomes: month = 9, date = 18, year = 2013

Not elegant, because one needs three inputs instead of 1, and three columns in the SQL table instead of 1.

I started looking for a better way and found a few ideas, none of which I really liked. I considered an idea of creating a single large number which would then be converted mathematically into the final three numbers. These three would then be converted using PHP date() function into the final display form. Example:

September 18th, 2013 becomes: date = 09182019


I still didn't like this, because it required some complicated mathematical code, plus a second step of the date() function. I finally gave up on storing as a number, and looked into characters. Storing as:

September 18th, 2013 = date = '9-18-2013'

This might be able to plug directly into date(), saving time. In the process of testing I discovered that if the input format was changed into '2013-9-18' (which makes more sense from a logical perspective), it could indeed be placed directly into date(), saving considerable time. Success for step 1!

This left me with the issue of figuring out how to get the next/previous day. I was hoping to avoid doing some custom function writing. It would require an array of the max days in each month, to figure out if the next day was actually the 1st. Plus a math function to figure out leap years. I was saved from this by the internet, and I found that the php function strtotime() could do it for me!

  $previousDay= date("F dS, Y",strtotime($specifiedDate." -1 day"));

The outputs into the correct format, and does the math for me, no matter what is the specified date. the final code, after pulling the raw data with a query, ends up being:

  $displayDateInfo = date("F dS, Y",strtotime($dateCF." -1 day"));
  $displayDateCF = date("F dS, Y",strtotime($dateCF));
  $displayDateInt = date("F dS, Y",strtotime($dateCF." +1 day"));
  $displayDateReg = date("F dS, Y",strtotime($dateReg));

Clean, concise, and with half the columns used up for storage.

No comments:

Post a Comment