Database Reference
In-Depth Information
}
$valid
=
$members
{
$val
};
For this method, the hash acts as a cache, so that you execute a lookup query for any
given value only once, no matter how many times it occurs in the input. For datasets
that have repeated values, this approach avoids issuing a separate query for every single
test, while requiring an entry in the hash only for each unique value. It thus stands
between the other two approaches in terms of the trade-off between database traffic and
program memory requirements for the hash.
Note that the hash is used in a different manner for this method than for the previous
method. Previously, the existence of the input value as a key in the hash determined the
validity of the value, and the value associated with the hash key was irrelevant. For the
hash-as-cache method, the meaning of key existence in the hash changes from “it's
valid” to “it's been tested before.” For each key, the value associated with it indicates
whether the input value is present in the lookup table. (If you store as keys only those
values that are found to be in the lookup table, you issue a query for each instance of
an invalid value in the input dataset, which is inefficient.)
12.10. Converting Two-Digit Year Values to
Four-Digit Form
Problem
You must convert years in date values from two digits to four digits.
Solution
Let MySQL do this for you, or perform the operation yourself if MySQL's conversion
rules aren't appropriate.
Discussion
Two-digit year values are a problem because the century is not explicit in the data values.
If you know the range of years spanned by your input, you can add the century without
ambiguity. Otherwise, you can only guess. For example, the date 10/2/69 would be
interpreted by most people in the US as as October 2, 1969. But if it represents Mahatma
Gandhi's birth date, the year is actually 1869.
One way to convert years to four digits is to let MySQL do it. If you store a date containing
a two-digit year, MySQL automatically converts it to four-digit form. MySQL uses a
transition point of 1970; it interprets values from 00 to 69 as the years 2000 to 2069, and
values from 70 to 99 as the years 1970 to 1999. These rules are appropriate for year
