[ Pobierz całość w formacie PDF ]

the flight. Obviously, Origin and Destination are columns in the Flights table,
with other columns for things like the date and time of the flight, the type of
aircraft, the flight number, and the various fares.
The Cities table contains a list of all the cities to which the airline flies. Thus,
both the Origin and Destination columns in the Flights table will just contain
ID's referring to entries in the Cities table. Now, consider the following queries.
To get a list of flights with their origins:
mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID;
http://www.webmasterbase.com/printTemplate.php?aid=228 26-12-00
Building a Database-Driven Web Site Using PHP and MySQL - WebmasterBase.com Side 84 af 99
+--------+-----------+
| Number | Name |
+--------+-----------+
| CP110 | Montreal |
| CP226 | Sydney |
| QF2026 | Melbourne |
... ...
To get a list of flights with their destinations:
mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Destination = Cities.ID;
+--------+----------+
| Number | Name |
+--------+----------+
| CP110 | Sydney |
| CP226 | Montreal |
| QF2026 | Sydney |
... ...
Now what if we wanted to list both the origin and destination of each flight with a
single query? Pretty reasonable, right? Here's a query you might think to try:
mysql> SELECT Flights.Number, Cities.Name, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID
-> AND Flights.Destination = Cities.ID;
Empty set (0.01 sec)
Why doesn't this work? Have another look at the query, this time focusing on
what it actually says, rather than what you expect it to do. You're telling MySQL to
join the Flights and Cities tables and list the flight number, city name, and city
name (yes, twice!) of all entries obtained by matching up the Origin with the City
ID and the Destination with the City ID. In other words, the Origin, Destination,
and City ID must all be equal! This results in a list of all flights where the origin
and the destination are the same! Unless your airline is offering scenic flights,
there aren't likely to be any entries matching this description (thus the "Empty
set" result above).
What we need is a way to be able to return two different entries from the Cities
table (one for the origin and one for the destination) for each result. If we had
two copies of the table, one called Origins and one called Destinations, this
would be much easier to do, but why maintain two tables containing the exact
same list of cities? The solution is to give the Cities table two different temporary
names (aliases) for the purposes of this query.
By following the name of a table with AS Alias in the FROM portion of the SELECT
query, we can give it a temporary name with which to refer to it elsewhere in the
query. Here's that first query again (to display flight numbers and origins only),
but this time we have given the Cities table an alias: Origins.
mysql> SELECT Flights.Number, Origins.Name
-> FROM Flights, Cities AS Origins
-> WHERE Flights.Origin = Origins.ID;
This doesn't actually change the way the query works -- in fact, it doesn't change
the results at all -- but for long table names, it can save some typing. Consider,
for example, if we had given aliases of F and O to Flights and Cities,
respectively. The query would be much shorter as a result.
Let's now return to our problem query. By referring to the Cities table twice,
http://www.webmasterbase.com/printTemplate.php?aid=228 26-12-00
Building a Database-Driven Web Site Using PHP and MySQL - WebmasterBase.com Side 85 af 99
using two different aliases, we can use a three-table join (where two of the tables
are actually one and the same) to get the effect we want:
mysql> SELECT Flights.Number, Origins.Name,
-> Destinations.Name
-> FROM Flights, Cities AS Origins,
-> Cities AS Destinations
-> WHERE Flights.Origin = Origins.ID
-> AND Flights.Destination = Destinations.ID;
+--------+-----------+----------+
| Number | Name | Name |
+--------+-----------+----------+
| CP110 | Montreal | Sydney |
| CP226 | Sydney | Montreal |
| QF2026 | Melbourne | Sydney |
... ... ...
You can also define aliases for column names. We could use this, for example, to
differentiate the two "Name" columns in our result table above:
mysql> SELECT F.Number, O.Name AS Origin,
-> D.Name AS Destination
-> FROM Flights AS F, Cities AS O, Cities AS D
-> WHERE F.Origin = O.ID AND F.Destination = D.ID;
+--------+-----------+-------------+
| Number | Origin | Destination |
+--------+-----------+-------------+
| CP110 | Montreal | Sydney |
| CP226 | Sydney | Montreal |
| QF2026 | Melbourne | Sydney |
... ... ...
GROUPing SELECT Results
Way back in Part Two, we saw the following query, which tells us how many jokes
are stored in our Jokes table:
mysql> SELECT COUNT(*) FROM Jokes;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
The MySQL function COUNT used in this query belongs to a special class of
functions called "summary functions" or "group-by functions", depending on [ Pobierz całość w formacie PDF ]