Viewing relationships

The goats app has these tables:

Tables

When users are looking at goat records, they want to see the names of the fave comedians, from the 1:N. They also want to see the clubs they're in, through the club_members table. For example, here's Bonnie's record.

Goat

The name of the fave comedian is shown, along with a link. Same for the clubs. Users can click on them, essentially jumping around the DB, following the relationships.

There can be only one fave comedian link, since it's a 1:N. But there can be many club links, since the goat can be in many clubs.

Here's a comedian's record:

Comedian record

There are links to several goats, because comedian is on the N side of the 1:N. One comedian can be the fave of many goats.

The other entity is the club.

Club

One club can have many goats, so you see more than one link here.

Making the views

Let's write PHP to make one of the pages. Let's start with comedian, since it's probably the easiest.

Here's that page again:

Comedian record

Here's the HTML for the page. The important bits, anyway.

<p>Here are the deets for the comedian.</p>
<p>Name: Aisling Bea</p>
<p>Comments: One of the best!</p>
<h3>Goats with this comedian as fave</h3>
<ul>
<li><a href='goat-view.php?goat_id=7'>Anthree</a></li>
<li><a href='goat-view.php?goat_id=6'>Blunl</a></li>
<li><a href='goat-view.php?goat_id=1'>Flothery</a></li>
<li><a href='goat-view.php?goat_id=10'>Movilla</a></li>
</ul>

There's a link for each goat that likes Aisling. The link is to the goat-view.php, and includes the goat's id.

Let's check out the code to make it.

print "<p>Here are the deets for the comedian.</p>\n";
print renderComedianDeets($comedianId);
...
/**
* Make HTML to render a club.
* @param int $comedianId The comedian's id.
* @return string|null HTML to show the club, or null if something broke.
*/
function renderComedianDeets($comedianId) {
$comedianEntity = getComedianWithId($comedianId);
if ( is_null($comedianEntity) ) {
return null;
}
$result = "<p>Name: {$comedianEntity['name']}</p>\n";
$result .= "<p>Comments: {$comedianEntity['comments']}</p>\n";
// Show the members.
$goats = getGoatsWithFaveComedian($comedianId);
if (is_null($goats)) {
$result .= '<p><strong>Something went wrong looking up goat who like this comedian.</strong></p>';
}
else {
if (count($goats) == 0) {
$result .= "<p><strong>This comedian is no goats' fave.</strong></p>";
}
else {
$result .= "<h3>Goats with this comedian as fave</h3>\n";
$result .= "<ul>\n";
foreach ($goats as $goat) {
$viewUrl = "goat-view.php?goat_id={$goat['goat_id']}";
$viewLink = "<a href='$viewUrl'>{$goat['name']}</a>";
$result .= "<li>$viewLink</li>\n";
}
$result .= "</ul>\n";
}
}
return $result;
}

The main page calls a render function, that generated the HTML to show the comedian's deets.

In the render function, Line 10 calls a fellow functiony friend to load the comedian's deets.

$comedianEntity = getComedianWithId($comedianId);

The next few lines output some basic fields. Line 17 is more interesting:

$goats = getGoatsWithFaveComedian($comedianId);

getGoatsWithFaveComedian() is a functiony friend. It loads goat data for goats whose fave comedian have the id given. Here it is.

/**
* Get the goats with a given comedian as their fave.
* @param int $comedianId The comedian's id.
* @return array|null Goats, or null if something broke.
*/
function getGoatsWithFaveComedian($comedianId) {
global $dbConnection;
$sql = "SELECT *
FROM goats
WHERE fave_comedian = :id
ORDER BY name;";
$stmnt = $dbConnection->prepare($sql);
$isQueryWorked = $stmnt->execute(['id' => $comedianId]);
if (!$isQueryWorked) {
return null;
}
$comedians = $stmnt->fetchAll();
return $comedians;
}

The SQL finds goats whose fave comedian is the value passed in.

Then we make links to those goats pages, in a ul list.

$result .= "<ul>\n";
foreach ($goats as $goat) {
$viewUrl = "goat-view.php?goat_id={$goat['goat_id']}";
$viewLink = "<a href='$viewUrl'>{$goat['name']}</a>";
$result .= "<li>$viewLink</li>\n";
}
$result .= "</ul>\n";

The code inside the loop, I could have put in one line, but I like to break things up into pieces. Easier to think about. Easy is good.

List of club members

Here's a club page, again.

Club

There a render function, renderClubDeets(). It has this line:

goats = getMembersInClub($clubId);

It loads data about the goats in the club. Here's that functiony friend.

/**
* Get the members in a club.
* @param int $clubId The club's id.
* @return array|null Members, or null if something broke.
*/
function getMembersInClub($clubId) {
global $dbConnection;
$sql = "SELECT goats.*
FROM club_members, goats
WHERE club_members.club_id = :id
AND club_members.goat_id = goats.goat_id";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$isQueryWorked = $stmnt->execute(['id' => $clubId]);
if (!$isQueryWorked) {
return null;
}
$goats = $stmnt->fetchAll();
return $goats;
}

Let's break down the SQL. It starts in the club_members table, finding the rows for that club.

WHERE club_members.club_id = :id

The it links to the goats table, using the goat ids of the club_members records it just selected.

club_members.goat_id = goats.goat_id

From those goat records, it returns all the fields.

SELECT goats.*

The query runs, and if all is OK, the function returns the goats who are in the club.

Here's a drawing for that query.

SELECT goats.*
FROM club_members, goats
WHERE club_members.club_id = :id
AND club_members.goat_id = goats.goat_id

Query drawing

Then it's just a matter of making links to the goats.

$result .= "<h3>Members</h3>\n";
$result .= "<ul>\n";
foreach ($goats as $goat) {
$viewUrl = "goat-view.php?goat_id={$goat['goat_id']}";
$viewLink = "<a href='$viewUrl'>{$goat['name']}</a>";
$result .= "<li>$viewLink</li>\n";
}
$result .= "</ul>\n";

Exercise

Wombat games

Make an app like this one.

Create two more tables in your wombat DB.

The games table shows games. Here's my definition of it. It just has an id and a name.

Games

Create another table to represent a many-to-many relationship between games and wombats. Each wombat can play many (or no) games, and one game can be played by many (or no) wombats. I named the table plays, but you can call it something else. Here's my definition.

Plays

Put some test data in your new tables.

Here are the files I created for the app:

Files

You don't have to use this structure.

There are four pages. First, a page to show a list of wombats.

Wombats list

Click a link, and see the deets for that wombat, including a list of the games that wombat plays:

Wombat deets

If a wombat plays no games, show a message to that effect.

Wombat plays no games

Click on a game name, and see the deets for that game, including a list of the wombats who play that game:

Game deets

If no wombats play the games, show a message to that effect.

No wombats

There should also be a page to show a list of games.

Games list

Click on a link to see the deets for that game.

Submit the URL of your solution, and a zip of your files. The usual coding standards apply.

If you were logged in as a student, you could submit a solution to this exercise.

Up next

This lesson explained how to make pages showing relationships between entities. How do you edit those relationships? That's what the next lesson it about.