HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [267]
This code is almost like the query shown in Figure 4-3, except it uses a mathematical operator. You can use most of the math operators in queries to do quick conversions. Now, the age is specified in years, but the decimal part is a bit odd. Normally, you either go with entire year measurements or work with months, weeks, and days.
Figure 4-4: You can divide by 365 to determine the number of years.
Converting the days integer into a date
The YEAR() function extracts only the years from a date, and the MONTH() function pulls out the months, but both these functions require a date value. The DATEDIFF() function creates an integer. Somehow, you need to convert the integer value produced by DATEDIFF() back into a date value. (For more on this function, see the section “Using DATEDIFF to determine age,” earlier in this chapter.)
Figure 4-5 is another version of a query that expresses age in terms of years and months.
Figure 4-5: The age is now converted back to a date.
This query takes the DATEDIFF() value and converts it back to a date. The actual date is useful, but it has some strange formatting. If you look carefully at the dates, you’ll see that they have the age of each hero, but it’s coded as if it were a particular date in the ancient world.
Using YEAR() and MONTH() to get readable values
After you’ve determined the age in days, you can use the YEAR() and MONTH() functions to pull out the hero’s age in a more readable way, as illustrated by Figure 4-6.
Figure 4-6: The YEAR(), MONTH(), and DAY() functions return parts of a date.
The query is beginning to look complex, but it’s producing some really nice output. Still, it’s kind of awkward to have separate fields for year, month, and day.
Concatenating to make one field
If you have year, month, and day values, it would be nice to combine some of this information to get a custom field, as you can see in Figure 4-7.
There’s no way I’m writing that every time. . . .
I know what you’re thinking. All this fancy function stuff is well and good, but there’s no stinkin’ way you’re going to do all those function gymnastics every time you want to extract an age out of the database. Here’s the good news: You don’t have to. It’s okay that the queries are getting a little tricky because you’ll write code to do all the work for you. You write it only once, and then your code does all the heavy lifting. Generally, you write PHP code to manage each query inside a function. Once you’ve tested it, you run that function and off you go. . . . You can also use a little gem called the view, described in the “Creating a View” section. Views allow you to store complex queries right in your database.
Figure 4-7: Now, the age is back in one field, as originally intended.
This query uses the CONCAT() function to combine calculations and literal values to make exactly the output the user is expecting. Even though the birthday is the stored value, the output can be the age.
Creating a View
The query that converts a birthday into a formatted age is admittedly complex. Normally, you’ll have this query predefined in your PHP code so that you don’t have to think about it anymore. If you have MySQL 5.0 or later, though, you have access to a wonderful tool called the VIEW. A view is something like a virtual table.
The best way to understand a view is to see a sample of it in action. Take a look at this SQL code:
CREATE VIEW heroAgeView AS
SELECT
name as ‘hero’,
CONCAT(
YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))),
‘ years, ‘,
MONTH(FROM_DAYS(DATEDIFF(NOW(), birthday))),
‘ months’
) AS ‘age’
FROM
hero;
So what if I’m stuck with MySQL 4.0?
Views are so great that it’s hard to imagine working with data without them. However, your hosting service may not have MySQL 5.0 or later installed,