• Choose a category
• All
• Classic-Fiction

The most common approach is to store all the queries you’re likely to need (the ones that would be views) as strings in your PHP code. Execute the query from PHP, and you’ve essentially executed the view. This method is how most programmers did it before views were available in MySQL.

Another approach is to create a new table called something like storeQuery in your database. Put the text of all your views inside this table, and then you can extract the view code from the database and execute it using a second pass at the data server.

If you look closely, it’s exactly the same query used to generate the age from the birth date, just with a CREATE VIEW statement added. When you run this code, nothing overt happens, but the database stores the query as a view called heroView. Figure 4-8 shows the cool part.

Figure 4-8: This simple query hides a lot of complexity.

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, described in the sidebar “So what if I’m stuck with MySQL 4.0?”

♦ 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.

Using an Inner Join to Combine Tables

When I normalized the hero database in Chapter 3 of this minibook, I broke it up into several tables. Take a quick look at the hero table in Figure 4-9.

You probably noticed that most of the mission information is now gone from this table, except one important field. The missionID field is an integer field that contains the primary key of the mission table. A foreign key is a field that contains the primary key of another table. Foreign keys are used to reconnect tables that have been broken apart by normalization.

Look at the mission table in Figure 4-10, and the relationship between the mission and hero tables begins to make sense.

Figure 4-9: The hero table has a link to the mission table.

Figure 4-10: The mission table handles mission data but has no link to the hero.

The mission table doesn’t have a link back to the hero. It can’t. because any mission can be connected to any number of heroes, and you can’t have a listed field.

Building a Cartesian join and an inner join

Compare the hero and mission tables, and you see how they fit together. The missionID field in the hero table identifies which mission the hero is on. None of the actual mission data is in the hero field, just a link to which mission the player is on.

Creating a query with both tables, as in Figure 4-11, is tempting. This query appears to join the tables, but it obviously isn’t doing the right thing. You have only three heroes and two missions, yet this query returns six rows! What’s happened here is called a Cartesian join. It’s a combination of all the possible values of hero and mission, which is obviously not what you want.

You don’t really want all these values to appear; you want to