Calendar Pick-A-Date Scripts Reviewed

This week I needed to make a form where the user needed to enter two dates in order to produce a report with the selected start and end dates. What were my options?

I considered writing my own php script where I would have separate pull-down boxes for the day, month and year to assure that there was no mistake as to the actual dates. That would take a little extra format massaging to create the date in MySQL format, so I looked for scripts that others had already written. Who wouldn’t?

Formatting dates can drive a person crazy. Does ’06-08-2000′ mean June 8th or the 6th of August? Writing for the Web should be universal. The month-day format that we tend to use in the U.S. is confusing to everybody else who uses the day-month format, and vice-versa.

Writing by hand I like to use this format ’12-Mar-2000′ so there is no ambiguity, but that format needs to be converted to ‘YYYY-MM-DD’ format when sending dates to MySQL.

So, what are my requirements for such a Date-Picker Script?

  • easy, intuitive for the user
  • easy for me with output in MySQL format (YYYY-MM-DD)
  • unrestricted license would be nice

I tried a few different date picker scripts and decided upon SpiffyCalendar because it’s the most advanced, yet easily modified to suit my purposes. Also, there is an established support system via a Yahoo! user group. That’s really nice, and unexpected.

Here’s the scripts I tried and the pros and cons for each…most weren’t as user-friendly as SpiffyCalendar.

 

PHP Calendar

    PROS

  • I like the look of this calendar, but it’s difficult to determine how to modify the calendar other than for css of calendar appearance.
  • Date output is already in MySQL format: YYYY-MM-DD
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Ugly and excessively long query strings in links.

DatePick

    PROS

  • I like the little calendar icon.
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Timestamp is included. Don’t need it.
  • Relies on javascript and pop-up window to select date.
  • Calendar doesn’t update well when moving from month to month. Part of the calendar doesn’t get loaded in so all days are not selectable. Have to manually widen window to see all days.
  • Can’t select year directly, have to click through months to get to next year.
  • No directions on how to modify output.

DateTimePick

    PROS

  • I like the little calendar icon.
  • Can modify output via provided directions.
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Relies on javascript and pop-up window to select date.
  • Calendar doesn’t update well when moving from month to month. Part of the calendar doesn’t get loaded in so all days are not selectable. Have to manually widen window to see all days.
  • Can select year directly, but calendar doesn’t update well.

SpiffyCalendar

Sign up to the Yahoo! Group to download the script. Script used to be offered at the now defunct GeoCities. The above link is the first place I found it posted afterwards.

    PROS

  • Calendar pops into page, not a separate window.
  • Can modify output via provided directions.
  • Can get help at Yahoo! Groups.
  • Unrestricted license.
  • Can refresh page containing calendars as link is not modified after picking a date or two.
  • Highlights holidays, although will have to remove Dec 1st developer’s birthday. (OK, modified holiday array in script on lines 86-87.)
    CONS

  • Format of date will have to be modified for MySQL. (OK, Added "cal1.dateFormat="yyyy-MM-dd"; after instantiation in header for calendar named ‘cal1’.)
  • Relies on javascript.
  • Had to modify the images folder location in the .js file to an absolute address, although things worked fine on my local server with a relative address.

I’m sure there are plenty of these date-picker scripts floating around the Web. Do you have a different one to share with us? Leave a comment!

Limit of 127 Points to Wrong Numeric Type

Sometimes the simple things evade us when we’re caught up in the details. Thank goodness for manuals.

It turns outs that the numeric type that you select for your database variables matters greatly – DUH!

I had an occurrence last week where a client entered values over 200 in a form, yet when the numbers were stored in the MySQL database were capped at 127. At first the error wasn’t spotted but when multiple entries were reported with the same 127, it became obvious that something was wrong.

My error was using tinyint instead of smallint as the numeric data type when setting up the MySQL table.

The MySQL Manual clearly gives us limits for each data type, as found in the following table:

Numeric Data Types in MySQL
Numeric Data Types in MySQL

I don’t think we’ll have an occasion to use BIGINT, but the other data types will definitely be of use.

When all else fails, RTFM!

Left Joins Produce Data Reports Using All Rows of One Table

A client needed to have several reports created that indicate the various training sessions and certifications that each member has achieved. Producing the reports required collating data from several mysql tables and that made the queries a little complex.

SELECT statements were used for the most part which combined data from up to nine different mysql tables for the most inclusive report. A problem surfaced in that the reports did not show all the members. Some of the newer members did not yet have accomplishments that would show up in the output. Instead of seeing blank values for the newbies, the output did not list those new people at all. The reports were incomplete without listing all the members.

Not all members are listed in the report.

It would be helpful to see those blanks in the report as a reminder that some of the new people still need to satisfy certain requirements.

LEFT JOINS to the rescue!

LEFT JOINs are used to combine data from several database tables that may or may not have content pertaining to all members. Using the LEFT JOIN having the members table on the left will retain all members in the final report whether or not they have entries in the associated tables.

Instead of using the regular construct, SELECT data-col1, data-col2, data-col3 FROM table1, table2 WHERE condition1 = condition2, a LEFT JOIN was used like so:

SELECT data-col1, data-col2, data-col3 FROM table1 LEFT OUTER JOIN table2 ON condition1 = condition2

where the two table names are separated by “LEFT OUTER JOIN” and the WHERE clause is changed to the ON condition.

Emphasis is given to the table named on the left, table1, so that all its rows will be retained through the SELECTing of columns even though null values are encountered.

Original code that produced a report without blank lines:

$fems = mysql_query(“SELECT last_name, first_name, MAX(certs_ID) AS mcrt, fema_date, pin FROM members, fema, fema_attend, certs, sar-certs WHERE pin = mem_pin AND fema_ID = fema_ids AND member_pin = pin AND cert_ID = certs_ID GROUP BY last_name, first_name “)

Updated code using LEFT JOINS that produced a complete report that contained blank lines:

$fems = mysql_query(“SELECT last_name, first_name, MAX(certs_ID) AS mcrt, fema_date, pin FROM members LEFT OUTER JOIN fema_attend ON pin = mem_pin LEFT OUTER JOIN fema ON fema_ID = fema_ids LEFT OUTER JOIN sar-certs ON member_pin = pin LEFT OUTER JOIN certs ON cert_ID = certs_ID GROUP BY last_name, first_name “)

All members present in the report using LEFT JOINs.

You’ll need a new LEFT JOIN for each table that you want to tack onto the SELECT statement in order to keep the blank values in the output.

Keep smiling and keep reading!

Null Values Handled Nicely with the PHP Error Suppression Operator

Working on some database reports for a client’s membership roster and such, I came across a simple solution in handling some types of errors. Error messages filled a column in one report that should have output whether certain requirements were met by individual members of the organization.

Here’s the queries and partial output in question:

$fems = mysql_query(“SELECT last_name, first_name, MAX(certs_ID) AS mcrt, fema_date, pin FROM members LEFT OUTER JOIN fema_attend ON pin = mem_pin LEFT OUTER JOIN fema ON fema_ID = fema_ids LEFT OUTER JOIN sar-certs ON member_pin = pin LEFT OUTER JOIN certs ON cert_ID = certs_ID GROUP BY last_name, first_name “) or die (“Query failed here 12”);
while ($fem = mysql_fetch_array($fems)) {
echo “<td>$fem[last_name]</td>”.” <td>$fem[first_name]</td> “;

$high_cert = mysql_query(“SELECT cert FROM certs WHERE cert_ID = $certif[mcert]”);
while ($high_cer = mysql_fetch_array($high_cert)) {
echo “$high_cer[cert]”;
}…

And, here’s part of the ugly report that code section produced:

Ugly report.

What’s happening here to produce the error is that one of the queries produces some null values for the newest members of the organization who haven’t completed some requirements as of yet.

While fetching values from the $fems array an error is dumped on the screen that states:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\org_member.php on line 766

How did I figure out what that error meant? Using the tool phpmyadmin! Once you open phpMyAdmin and have selected your database, click on the SQL tab.

phpMyAdmin and its SQL tab.

I copied and pasted the first SELECT statement in the SQL box that is labeled “Run SQL query/queries on database psar-data:”, where psar-data is the name of my database.
SQL query inside phpMyAdmin.SQL query inside phpMyAdmin.

The page that is returned after pressing Go shows the SQL query and its output that contains NULL values.

phpMyAdmin and its SQL tab and query.

Now, when the data set was subjected to the second query the null values produced the invalid result resource warning error. The second query was this:

$high_crts = mysql_query(“SELECT cert FROM certs WHERE cert_ID = $fem[mcrt]”);
while ($high_crt = mysql_fetch_array($high_crts)) {
echo “$high_crt[cert]”;
}

Outputting values to the screen from this query would be so much nicer if the error would go away and just leave a blank. This example is a perfect use for the @ operator, or PHP error suppression operator. When placed in front of a PHP function the @ operator does not output error statements and mess up your data reports.

In the case where null values are encountered by the PHP function mysql_fetch_array the ugly warning error is produced. Placing the @ operator directly in front of mysql_fetch_array, like so @mysql_fetch_array, gives the output we’re seeking.

Output using the @ operator.

Yes, that output looks more professional without the error message and shows exactly what we want. In this case the blanks are meaningful and show exactly where a member needs to complete certain requirements.