Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

Dealing with Multiple Tables

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!