• Choose a category
• All
• Classic-Fiction

Here are some thoughts about this type of query:

♦ It combines three tables. That complexity seems scary at first, but it’s really fine. The point of this query is to use the hero_power table to identify relationships between hero and power. Note that the FROM clause lists all three tables.

♦ The WHERE clause has two links. The first part of the WHERE clause links up the hero_power table with the hero table with an inner join. The second part links up the power table with another inner join.

♦ You can use another AND clause to further limit the results. Of course, you can still add other parts to the AND clause to make the results solve a particular problem, but I leave that alone for now.

Figure 4-18 shows the result of this query. Now, you have results you can use.

Figure 4-18: The Link Query joins up heroes and powers.

Once again, this query is an obvious place for a view:

CREATE VIEW heroPowerView AS

SELECT

hero.name AS ‘hero’,

power.name AS ‘power’

FROM

hero, power, hero_power

WHERE

hero.heroID = hero_power.heroID

AND

power.powerID = hero_power.powerID;

Typically, you won’t do your results exactly like this view. Instead, you display information for, say, Binary Boy, and you want a list of his powers. It isn’t necessary to say Binary Boy three times, so you tend to use two queries (both from views, if possible) to simplify the task. For example, look at these two queries:

SELECT * FROM heroMissionView WHERE hero = ‘binary boy’;

SELECT power FROM heroPowerView WHERE hero = ‘binary boy’;

The combination of these queries gives you enough data to describe everything in the original table. Typically, you attach all this data together in your PHP code. Figure 4-19 shows a PHP page using both queries to build a complete picture of Binary Boy.

Figure 4-19: Use two different queries to get the formatting you want.

The code is standard PHP data access, except it makes two passes to the database:

“-//W3C//DTD XHTML 1.0 Strict//EN”

“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>

//connect

$conn = mysql_connect(”localhost”, ”xfd”, ”password”);

//change this password and username to work on your system

mysql_select_db(”xfd”);

//get most information for requested hero

$hero = ”binary boy”;

$query = <<

SELECT

*

FROM

heroMissionView

WHERE

hero = ’$hero’

HERE;

print ”

\n”;

$result = mysql_query($query, $conn);

$row = mysql_fetch_assoc($result);

foreach ($row as $field => $value){

print <<

$field

$value

HERE;

} // end foreach

print ”

powers

\n”;

print ”

\n”;

print ”

\n”;

//create another query to grab the powers

$query = <<

SELECT

power

FROM

heroPowerView

WHERE hero = ’$hero’

HERE;

//put powers in an unordered list

$result = mysql_query($query, $conn);

while ($row = mysql_fetch_assoc($result)){

foreach ($row as $field => $value){

print ”

• $value
\n”;

} // end foreach

} // end while loop

print ”

\n”;

print ”

\n”;

print ”

?>

Refer to Book V to read more on PHP and how it’s used to access databases.

Book VII

Into the Future with AJAX

Chapter 1: AJAX Essentials

In This Chapter

Understanding AJAX

Using JavaScript to manage HTTP requests

Creating an XMLHttpRequest object

Building a synchronous AJAX request

Retrieving data from an AJAX request

Managing asynchronous AJAX requests

If you’ve been following the Web trends, you’ve no doubt heard of AJAX. This technology has generated a lot of interest. Depending