How to Create a View in SQL for HTML5and CSS3 Programming

The query that converts a birthday into a formatted age in SQL is admittedly complex for HTML5 programming. 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;

If you look closely, it's exactly the same query used to generate the age from the birth date, just with a CREATEVIEW statement added. When you run this code, nothing overt happens, but the database stores the query as a view called heroView.

This code doesn't look really fancy, but look at the output. It's just like you had a table with all the information you wanted, but now the data is guaranteed to be in a decent format.

After you create a view, you can use it in subsequent SELECT statements as if it were a table! Here are a couple of important things to know about views:

• They aren't stored in the database. The view isn't really data; it's just a predefined query. It looks and feels like a table, but it's created in real time from the tables.

• You can't write to a view. Because views don't contain data (they reflect data from other tables), you can't write directly to them. You don't use the INSERT or UPDATE commands on views, as you do ordinary tables.

• They're a relatively new feature of MySQL. Useful as they are, views weren't added to MySQL until Version 5.0. If your server uses an earlier version, you'll have to do some workarounds.

• You can treat views as tables in SELECT statements. You can build SELECT statements using views as if they were regular tables.

Some database packages make it appear as though you can update a view, but that's really an illusion. Such programs reverse-engineer views to update each table. This approach is far from foolproof, and you should probably avoid it.