Format dates for entry into MySQL using PHP

The best way to handle the input of dates is with separate fields using HTML <select> elements or combo boxes. Spell out the name of the months in full, and assign the month number as the value. For example, in an HTML form:

<select name="month" id="month">
  <option value="1">January</option>
  <option value="2">February</option>
  <!-- remaining months omitted -->
</select>

Assuming that the input elements are called year, month, and day, and are submitted using the POST method, you can build the date in your processing script like this:

$date = $_POST['year'] . '-' . $_POST['month'] . '-' .
$_POST['day'];

There's just one problem with this: unless you use JavaScript (or ActionScript) to control the number of days displayed for each month, someone might enter an invalid date, such as February 31 or September 31. So, it's a good idea to use the PHP checkdate() function to make sure the date is valid. This can be incorporated into a custom function to do everything in one pass like this:

function date2mysql($year, $month, $day) {
  return checkdate($month, $day, $year) ? "$year-$month-$day" :
false;
}

This returns the date, if valid, correctly formatted for MySQL. If the date is invalid, the function returns false. You can then use this to prepare an error message if an impossible date has been entered by the user.

$date = date2mysql($_POST['year'], $_POST['month'], $_POST['day']);
if (!$date) {
  $error = 'Invalid date';
}

Processing a specific format

There are occasions when you can be confident users will adhere to a specific format, such as MM/DD/YYYY, for example in an intranet, or when a date picker is used to insert dates into form fields.

The following custom PHP function accepts dates not only formatted as MM/DD/YYYY, but also accepts dates that use a hyphen, colon, period, or space as the separator. It returns the date, if valid, correctly formatted for MySQL. Otherwise, it returns false.

function mdy2mysql($input) {
  $output = false;
  $d = preg_split('#[-/:. ]#', $input);
  if (is_array($d) && count($d) == 3) {
    if (checkdate($d[0], $d[1], $d[2])) {
      $output = "$d[2]-$d[0]-$d[1]";
    }
  }
  return $output;
}

For countries that use the European convention of DD/MM/YYYY, use the following function instead:

function dmy2mysql($input) {
  $output = false;
  $d = preg_split('#[-/:. ]#', $input);
  if (is_array($d) && count($d) == 3) {
    if (checkdate($d[1], $d[0], $d[2])) {
      $output = "$d[2]-$d[1]-$d[0]";
    }
  }
  return $output;
}

Both functions work the same way, accepting the date input as a string. If the date field is submitted by the POST method, use it like this:

$date = mdy2mysql($_POST['date']);
if (!$date) {
  $error = 'Invalid date';
}

For dates submitted using the European format, use dmy2mysql() instead of myd2mysql().

The danger in using a single date field for user input is that a European visitor to an American site might insert a date such as 7/4/2010, intending it to be interpreted as 7 April 2010, but actually ending up with inserting July 4, 2010. When both the month and day parts are less than 13, checkdate() declares the date as valid, even though the way it's interpreted might be completely different from the user's intention. That's why separate input fields for each part of the date remain the best solution.

NOTE: MySQL inserts leading zeros automatically if they are omitted from the input. It's the order of the date parts that is crucial to the correct storage of dates in MySQL.

Using these techniques with Dreamweaver server behaviors

Dreamweaver server behaviors cease to be editable through the user interface if you edit them in Code view. To avoid such problems, process the date formatting in a separate PHP code block at the top of the page. Reassign the result of the formatting function to the name of the $_POST variable that you want to insert in the database. For example, let's say your date column is called dob, use the functions like this:

$_POST['dob'] = date2mysql($_POST['year'], $_POST['month'],
$_POST['day']);
if (!$_POST['dob']) {
  $error = 'Invalid date';
}

This recipe assigns an error message to $error if an invalid date is input. It's up to you to create an error checking mechanism (use a conditional statement) to prevent the server behavior from being executed if the date fails the checking process.