With your data now separated into two tables, it may seem that you're complicating the process of data retrieval. Consider, for example, our original goal: to display a list of jokes with the name and email address of the author next to each joke. In the single-table solution, you could get all the information you needed to produce such a list using a single SELECT statement in your PHP code:
$jokelist = mysql_query(
"SELECT JokeText, AuthorName, AuthorEMail FROM Jokes");
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke["JokeText"];
$name = $joke["AuthorName"];
$email = $joke["AuthorEMail"];
// Display the joke with author information
echo( "<p>$joketext<br></br>" .
"(by <a href=''>$name</a>)</p>" );
}
In the new system, this would, at first, no longer seem possible. As the details about the author of each joke aren't stored in the Jokes table, you might think that you'd have to fetch those details individually for each Joke you wanted to display. The code to do perform this task would look like:
// Get the list of jokes
$jokelist = mysql_query("SELECT JokeText, AID FROMJokes");
while ($joke = mysql_fetch_array($jokelist)) {
// Get the text and Author ID for the joke
$joketext = $joke["JokeText"];
$aid = $joke["AID"];
// Get the author details for the joke
$authordetails = mysql_query(
"SELECT Name, EMail FROM Authors WHERE ID=$aid");
$author = mysql_fetch_array($authordetails);
$name = $author["Name"];
$email = $author["EMail"];
// Display the joke with author information
echo( "<p>$joketext<br></br>" .
"(by <a href=''>$name</a>)</p>" );
}
It's pretty messy, and it involves a query to the database for every single joke that's displayed, which could slow down the display of your page considerably. With all this taken into account, it would seem that the "old way" was actually the better solution, despite its weaknesses. Fortunately, relational databases like MySQL are designed to make working with data stored in multiple tables easy! Using a new form of the SELECT statement, called a join, you can have the best of both worlds. Joins allow you to treat related data in multiple tables as if they were stored in a single table. Here's what the syntax of a simple join looks like:
mysql>SELECT columns FROM tables ->WHERE condition(s) for data to be related;
In your case, the columns you're interested in are JokeText in the Jokes table, and Name and EMail in the Authors table. The condition for an entry in the Jokes table to be related to an entry in the Authors table is that the value of the AID column in the Jokes table is equal to the value of the ID column in the Authors table. Here's an example of a join (the first two queries simply show you what's contained in the two tables — they aren't necessary):
mysql>SELECT LEFT(JokeText,20), AID FROM Jokes;
+----------------------+------+
| LEFT(JokeText,20) | AID |
+----------------------+------+
| Why did the chicken | 1 |
| A man walked into a | 1 |
| Knock knock. Who's t | 2 |
+----------------------+------+
3 rows in set (0.00 sec)
mysql>SELECT * FROM Authors;
+----+------------+---------------------+
| ID | Name | EMail |
+----+------------+---------------------+
| 1 | Kevin Yank | kyank@attglobal.net |
| 2 | Joan Smith | joan@somewhere.net |
+----+------------+---------------------+
2 rows in set (0.00 sec)
mysql>SELECT LEFT(JokeText,20), Name, EMail
->FROM Jokes, Authors WHERE AID = Authors.ID;
+----------------------+------------+---------------------+
| LEFT(JokeText,20) | Name | EMail |
+----------------------+------------+---------------------+
| Why did the chicken | Kevin Yank | kyank@attglobal.net |
| A man walked into a | Kevin Yank | kyank@attglobal.net |
| Knock knock. Who's t | Joan Smith | joan@somewhere.net |
+----------------------+------------+---------------------+
3 rows in set (0.00 sec)
See? The results of the third SELECT, which is a join, group the values stored in the two tables into a single table of results, with related data correctly appearing together. Even though the data is stored in two tables, you can still get all the information you need to produce the joke list on your Web page with a single database query. Note in the query that, since there are columns named ID in both tables, you must specify the name of the table when you refer to the ID column in the Authors table (Authors.ID). If you don't specify the table name, MySQL won't know which ID you're referring to, and will produce this error:
mysql>SELECT LEFT(JokeText,20), Name, EMail
->FROM Jokes, Authors WHERE AID = ID;
ERROR 1052: Column: 'ID' in where clause is ambiguous
Now that you know how to access the data stored in your two tables efficiently, you can rewrite the code for your joke list to take advantage of joins. The following is reproduced with complete error checking (which has been omitted here for brevity) in jokelist2.php in the code archive.
$jokelist = mysql_query(
'SELECT JokeText, Name, EMail
FROM Jokes, Authors WHERE AID=Authors.ID');
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke['JokeText'];
$name = $joke['Name'];
$email = $joke['EMail'];
// Display the joke with author information
echo( "<p>$joketext<br></br>" .
"(by <a href=''>$name</a>)</p>" );
}
The more you work with databases, the more you'll come to realize just how powerful this simple ability to combine data contained in separate tables into a single table of results really is. Consider, for example, the following query, which displays a list of all jokes written by Joan Smith:
mysql>SELECT JokeText FROM Jokes, Authors WHERE
->Name="Joan Smith" AND AID=Authors.ID;
The results that are output from the above query come only from the Jokes table, but the query uses a join to let it search for jokes based on a value stored in the Authors table. There will be plenty more examples of clever queries like this throughout this book, but this example alone illustrates that the practical applications of joins are many and varied, and in almost all cases can save you a lot of work!