Category Archives: MySQL

07.03.12
by

wp-SwimTeam v1.33.949 now available

After a day of testing on the MacDolphins site without any issues, I have released v1.33.949.  There are no changes between this build and the v1.33 beta build except the version number and the removal of the beta tag.

This build fixes a number of problems related to swimmer and user data which surfaced after I changed the database queries I was using in v.1.32.

  • Fixed bug which prevented generating roster report.
  • Fixed bug which prevented scratching swimmers from Meet tab.
  • Added additonal table to Meet Report when operating in Stroke mode which reports number of swimmers Opting In or Opting Out per age group.
  • Completed first phase Meet Entries export in Hy-tek HY3 format. Not exposed on the GUI yet.
  • Fixed bug which prevented Job Reminder emails from being sent.

You can find the update on the Download & Installation page or in the WordPress Plugin Repository.

06.30.12
by

wp-SwimTeam v1.32.931 now available

After a few days of testing on the MacDolphins site without any issues, I have decided to go ahead and release v1.32.931.  There are no changes between this build and the beta build except the version number and the removal of the beta tag.

The primary feature in this update is new SQL which addresses performance problems when working with user data, particularly when is relates to swimmers.  The Job Commitment Report was particularly noticeable in how slow it was (4-5 minutes) if it completed at all.  The SQL addresses that performance problem however since it used extensively across the plugin I wanted to make sure I hadn’t broken something else.

You can find the update on the Download & Installation page or in the WordPress Plugin Repository.

05.17.11
by

WordPress 3.2 will require PHP5

WordPress 3.2 beta is out for testing and with it comes several requirements.  The two notable requirements are PHP 5.2.14 or newer and MySQL 5.0 or newer.  I will likely move to 3.2 for wp-SwimTeam development once it is released and I’ve done some testing with it.  I’ve been running under PHP5 (5.2.14 and 5.3.x) for a while now so I don’t see this as an issue but it might be for people running on older web hosts.

05.27.08
by

Finally fixed age calculation

Over the weekend I was speaking with one of the swim team parents who was asking me about our web site and I showed her what she could see about her daughter.  As soon as I popped her daugher’s information she noticed that she was placed in the wrong age group.  Oops.

I had seen this situation previously but couldn’t pin it down but now that I had a swimmer I could test against, I dug into it.  It turns out that I had made the age calculation much, much, harder than it really needed to be.  And it was wrong.

I was trying to use MySQL’s PERIOD_DIFF() function so the age and cutoff date adjusted age would be returned as part of the query.  It turns out that PERIOD_DIFF() only accounts for months (which I knew when I used it) but not taking into account the days causes the calculation to be wrong and limits the age cutoff date to be the first  of a month which may or may not be acceptable.

My first search yielded this example which turns out to be wrong as it doesn’t always return the correct age.

Here is the correct SQL to calculate age:

SELECT YEAR(birthdate) - (MONTH(CURRENT_DATE()) < MONTH(birthdate)) -
    ((MONTH(CURRENT_DATE()) = MONTH(birthdate)) & (DAY(CURRENT_DATE()) <
    DAY(birthdate))) AS age 

Here is the correct SQL to calcualte age based on a specific cutoff date, in this case the cutoff date is June 1st:

SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) - (MONTH('2008-06-01') <
    MONTH(birthdate)) - ((MONTH('2008-06-01') = MONTH(birthdate)) &
    (DAY('2008-06-01') <= DAY(birthdate))) AS agegroupage

By using the calculations, the swimmer’s age and age group age are always returned with the query as opposed to computed on the fly.